Post reply

Use an Alias in a sql script

  • February 1, 2012 at 6:51 pm

    #254610

    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

    #1441553

    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

    #1441698

    Thank you 🙂 for your answer ,You save my time as i have the same Questions

    :hehe:

  • February 2, 2012 at 5:40 am

    #1441726

    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

    #1441730

    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

    #1441994

    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

    #1441996

    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 possible

    Cheers

    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