Post reply

Alias Name in Where Conditions

  • July 14, 2005 at 9:03 am

    #66732

    I am trying to use alias name in a where condition as below

    "SELECT Name, Code, (Basic*10) as Total FROM SALARIES WHERE Total > 1000"

    While trying the below statement i am getting Error "Invalid column name Total" .  In Sybase is working . Anyone can help me out on this in SQL SERVER 2000

    Thanks

    Krishna Reddy

     

  • July 14, 2005 at 9:08 am

    #574440

    SELECT Name, Code, (Basic*10) as Total FROM SALARIES WHERE Basic*10 > 1000

    You can resuse the alias only in the order by.. otherwise you must reuse the expression needed to create it.

  • July 14, 2005 at 9:27 am

    #574452

    Yes for ORDER BY it is working i have implemented in many queries, but for condition problem occurred , any way Thanks for the reply no way to run now . have replace big Queries in that place

  • July 14, 2005 at 9:29 am

    #574454

    Alright, HTH.

  • July 15, 2005 at 3:20 am

    #574708

    It helps to remember the logical order in which the clauses in a SQL statement are evaluated. The order is (I think): FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. The aliases are done in the SELECT clause so the WHERE clause won't know about wthem whereas the ORDER BY will.

    Itzik Ben-Gan does a very good seminar on this which I would recommend you go to if you ever get chance.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • July 15, 2005 at 8:26 am

    #574858

    You can use a subquery to define the aliases if you don't want to re-type the expression in other places.  This is not a correlated subquery that would affect performance, just a syntactical device that alters the scope of the alias name.

    SELECT * FROM (

       SELECT Name, Code, (Basic*10) as Total

       FROM SALARIES

    ) x

    WHERE Total > 1000

    If you check the execution plan you should find that it does not add any extra operations or change index usage, it just makes the query more readable and maintainable.

    I would be tempted to just use "WHERE Basic > 100" in this case, but this technique is useful whenever you have a repeated expression.

  • July 15, 2005 at 1:05 pm

    #575008

    My only problem with "WHERE Basic > 100" is that, although they are equivalent, you get some bleary-eyed programmer in at the end of a 13 hour day and it can cause confusion. I prefer consistancy even at the cost of efficiency in many things, the deciding factor being something that demands performance/efficiency.

    My current project/contract is kind of brute-force implementation, but it will be easily understood by anyone who has to follow me. I also have the advantage that it is not a transactional system: I pull data from various sources, reformat them, then spit out text files to upload to a web site. No querying, no need to work over execution plans.

    I'm a huge fan of clarity having to had to follow programmers where you look at their code and say "HUH?".

    That being said, I love your "Select() where...". Elegant, yet slightly obscure. 🙂

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • July 17, 2005 at 3:38 am

    #575105

    <Wayne>I prefer consistancy even at the cost of efficiency in many things, the deciding factor being something that demands performance/efficiency</Wayne>

     

    Amen to that!!!

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply