Alias Name in Where Conditions
-
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
-
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.
-
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
-
Alright, HTH.
-
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 -
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.
-
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] -
<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