February 1, 2012 at 6:51 pm
Hello,
I was wonering if it is possible to use an alias column in a script
eg:
Select (A1 + A2 + A2) as SumAmt, [SumAmt] * 5 as TotalAmount
Normally I would write this as
Select (A1 + A2 + A2) as SumAmt, (A1 + A2 + A2) * 5 as TotalAmount
Just a question....
Thanks
February 2, 2012 at 12:05 am
No, you can't.
You can reference the alias in an outer query, or in an ORDER BY, but not in another expression in the same SELECT clause.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 5:17 am
Thank you 🙂 for your answer ,You save my time as i have the same Questions
:hehe:
February 2, 2012 at 5:40 am
johnitech.itech (2/2/2012)
Thank you 🙂 for your answer ,You save my time as i have the same Questions:hehe:
Didnt save you any time as you could have wrote the exact same thing in ssms and tried it.
February 2, 2012 at 5:44 am
To answer a way you could do it in a single statment.
select a.SumAmt, (a.SumAmt * 5) as Totalamount
from
(Select (A1 + A2 + A2) as SumAmt) as a
February 2, 2012 at 11:46 am
No Problem, Thanks for the response... I understand that the request i put forward was not correct , I was wondering if things had changed in sql server to the extent that this type of syntax was possible
Cheers
February 2, 2012 at 11:54 am
andre-394971 (2/2/2012)
No Problem, Thanks for the response... I understand that the request i put forward was not correct , I was wondering if things had changed in sql server to the extent that this type of syntax was possibleCheers
The problem is the logic not the syntax. In order to do this sql would first have to execute the query, excluding the column(s) that are referencing the calculated column and then run a second query against the results.
For your example:
Select (A1 + A2 + A2) as SumAmt, [SumAmt] * 5 as TotalAmount
Sql would first have calculate A1 + A2 + A2 and store that information to use as the basis for the calculation in the second column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply