Does SQL permit use of a column alias directly in

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

Does SQL permit use of a column alias directly in

Author

Topic

matt52885
Starting Member

1 Post

Posted - 2010-04-29 : 12:33:59

Does SQL (2008) permit use of a column alias directly in the CASE statement?

Example: RetailPrice is a column alias. Created from Max(t6.price) As RetailPrice. I recieve the error invalid column name.

(Case
When AA.SALESNAME = '' and t1.PRODSTATUS = 7 or T1.PRODSTATUS = 5 then
SUM (RetailPrice * 0.60 * t1.QTYCALC)
Else 0
END) AS CompletedWOSalesOrder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-29 : 12:56:38

It isn't allowed like you have it coded. You can use a derived table in some cases though, but we'd need to see the actual query in order to determine what can be done.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 01:49:53

also in your above case condition you need braces around two ORs i guess
i.e it should be
When AA.SALESNAME = '' and (t1.PRODSTATUS = 7 or T1.PRODSTATUS = 5) then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 04:13:14

Any direct usage of alias name wont work unless it is used in ORDER BY clause (without CASE expression,calculation,etc)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources