Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Unanswered: SELECT column aliases: Refer to alias in another column?

    Using SQL Server 2000. How can I refer to one alias in another column?

    E.g., (this a contrived example but you get the idea)

    SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data

    Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It won't work. You have to use the full formula based on the base columns.

  3. #3
    Join Date
    Mar 2008
    Posts
    52
    But then you have to repeat the expression which seems bad from a maintenance perspective especially if the expression is more complex than my example (and maybe bad for performance?). Is there better alternative?

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    Computed columns is one option to consider.

  5. #5
    Join Date
    Mar 2008
    Posts
    52
    When defining computed columns can you refer to one computed column from another? Or is it similar to column aliases in that you can't?

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by tmpuzer
    When defining computed columns can you refer to one computed column from another? Or is it similar to column aliases in that you can't?
    no, you can't do that.

  7. #7
    Join Date
    Mar 2008
    Posts
    52
    Besides repeating the expression I have also seen the solution of using a derived table. Like:

    SELECT time, distance, speed, (speed / time) AS acceleration
    FROM (SELECT time, distance, (distance / time) AS speed FROM data)

    Between repeating the expression and using a derived table which do you think is the better solution? I think I like repeating the expression.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I like the derived table. copy/paste gets ugly fast, and is harder to maintain.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I use derived tables for this sort of thing. Recently wrote a query with about four levels of nested derived tables because of this issue but it is a lesser of evils by some margin for me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    delusional minds think alike.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Of course Ctrl + H can be handy too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    views, baby

    use nested views
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    views, baby

    use nested views
    pffft. get with it oldtimers, the new thing is CTE's.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    pffft. get with it oldtimers, the new thing is CTE's.
    You tried nesting CTEs?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    views, baby

    use nested views
    Why would you use a view if the business logic is limited in scope to the one query you are writing? Would you really recommend a view over a derived table here....baby?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •