Problem with Aliases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Aliases

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-11 : 01:04:39

Hi .

select count(e.employee_nbr) as total,
sum(case when e.Active_Flag=1 Then 0 else 1 end) as Active_Employees,
Total-Active_Employees As Inactve_Employees
FROM TEmployee e

When i Execute the Above query i get an error like below-

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Active_Flag'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Total'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Active_Employees'.

Cant i use alias names of result set for subtraction

Suggest me how can i do it.

Thanks in Advance
Swathi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 01:12:44

you cant directly use them. you need to make a derived table and then use from it

select total,Active_Employees,Total-Active_Employees As Inactve_Employees
from
(
select count(e.employee_nbr) as total,
sum(case when e.Active_Flag=1 Then 0 else 1 end) as Active_Employees
FROM TEmployee e
)t