what's wrong - 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

General SQL Server Forums

New to SQL Server Programming

what's wrong

Author

Topic

chinnanu143
Starting Member

13 Posts

Posted - 2010-11-25 : 04:38:04

here are two queries..
i want to do query like this but it's giving wrong answer

select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES where newsal >0

error
Msg 207, Level 16, State 1, Line 1
Invalid column name 'newsal'.

if i give like this it's coming

select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES where (salary+(salary *0.05)) >0

why it's not coming in first query

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-25 : 04:54:22

newsal is not a column but an alias and you can't use a where clause on aliases like this. This will do exactly the same:

select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES where salary >0
alternatively
select lastname ,salary * 1.05 as newsal from EMPLOYEES where salary > 0

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 04:54:57

Because the where clause is parsed before the resultset is created - newsal does not exist in the underlying table.

select * from
(
select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES
) a
where newsal >0

or
;with cte as
(
select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES
)
select *
from cte
where newsal >0

or why not
select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES where salary >0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chinnanu143
Starting Member

13 Posts

Posted - 2010-11-25 : 07:07:40

small change ...where salary >50000

here this salary should be new salary i mean after increment of 5%

Thanks
Prats
XX

chinnanu143
Starting Member

13 Posts

Posted - 2010-11-25 : 07:12:20

thanks for response

;with cte as
(
select lastname ,(salary+(salary *0.05)) as newsal from EMPLOYEES
)
select *
from cte
where newsal >0

can i know what method is this

Thanks
Prats
XX

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-25 : 07:17:37

This method is called a "common table expression" (hence the cte name) and is sort of an inline view definition. Very powerful if used correctly... -> http://msdn.microsoft.com/en-us/library/ms190766.aspx

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources