December 21, 2006 at 3:03 am
Hi to every one.
I have a query like as
SELECT Titleid AS tit FROM title WHERE tit=11
my query is that we can use the column alias in where condition
but my this query give me error : Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'tit'.
i want to clearfiy that sql server allow or does not allow column alias in the where clause.
if any body have other alternavite for that give me please .....
i have one alternative of alias by using subquery.
select Tit
from (
select titleid as tit
from title
) as T
where tit = 11
December 21, 2006 at 3:08 am
Would this not work?
SELECT Titleid AS tit FROM title WHERE Titleid=11
John
December 21, 2006 at 3:27 am
Dear Mr. or Ms.500 ,
it is not as simple as you think,
you have been given me the simple query , which any one can easilly apply. but you should first read my question before replying
well good efforts for reply... you have to learn lot in sql server........
December 21, 2006 at 4:02 am
It is not allowed that you can used a column alias in the Where clause.
Let us know why you need this or the requirement so that we can suggest you a good alternate solution.
cheers
December 21, 2006 at 9:04 am
Dear shashi,
it may be just bad choice of words caused by the fact that English is not your native language, but your remark is not very friendly or polite... I don't see how John's question, whether his SQL would not work, proves anything about his knowledge.
To your question - no, SQL Server does not allow use of column alias defined in SELECT in the WHERE clause of the same statement.
There are 2 ways to go about this : one is using derived table (as you posted), the other is using column name - as John posted (if the column in query is e.g. CASE statement, you'd have to repeat it whole again in WHERE clause):
SELECT CASE WHEN Titleid < 0 THEN 0 ELSE Titleid END AS tit
FROM title
WHERE CASE WHEN Titleid < 0 THEN 0 ELSE Titleid END =11
Hope this helps
Vladan
December 21, 2006 at 9:05 am
You cannot use a column alias in the WHERE.
The issue is the order that SQL Server performs a query. The query looks like it works in this order:
1. SELECT titleid AS tit
2. FROM title
3. WHERE tit = 11
But that's not how it works. It actually works....
1. FROM title
2. WHERE tit = 11
3. SELECT titleid as tit
What SQL Server does is first find the table, then everything thing that meets any condition (WHERE), then gets the columns that you want (SELECT). So, the alias does not exist at the time that the WHERE clause is resolved.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply