March 5, 2006 at 10:59 pm
Hi Everyone,
I have a question that hopefully has a quick answer.
if I use the following SQL;
SELECT tb.column AS alias
FROM tb
WHERE alias = 'myvalue'
I get an error stating that 'alias' is not a valid column.
Now, obviously I am doing something wrong - and of course if I was to use
WHERE tb.column = 'myvalue' - I get the results I expected.
If anyone can give me the LOWDOWN on why it is not working in the way I expected it to... I would be much appreciative!
Gavin Baumanis
Smith and Wesson. The original point and click device.
March 6, 2006 at 3:22 am
The where clause is executed before the aliases in the select are resolved. The only place (in a simple select) that you can use an alias defined in the select is in the order by statement.
Your alias declaration is fine, it's just that the alias name can't be used in the where. Use the original column name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2006 at 3:11 pm
Thanks for the assistance.
I ended up using the original column names as you suggested and of course, it all works fine and dandy.
Perhaps a WISHLIST entry about Aliases being able to be used throughout the whole SQL might be in order?
it's not a HUGE issue, it would just be nice to be able to use the same names trhoughoutt he whole SQL block. And yes I could always use a fully qualified column name throughout the SQL block but that's a whole lot of typing! - The SQL would ALWAYS work, but it's a whole lot of typing!
Gavin Baumanis
Smith and Wesson. The original point and click device.
March 7, 2006 at 8:16 am
Hi Gavin,
there is one thing you can do (it will come useful in more complex queries) - name the "inside" part of a query and use it as a derived table. The outer query will use aliases defined in inner query... Example will be better than long explaining:
SELECT Q.alias_b, Q.alias_a
FROM
(SELECT tb.column AS alias_b, ta.column AS alias_a
FROM tb
JOIN ta ON ta.col1 = tb.col1) AS Q
WHERE Q.alias_b = 'myvalue'
Generally, this is often a good solution when dealing with columns calculated in the query (both aggregate and other) and then using them in WHERE condition, since the code is more readable with "WHERE Q.production_costs > 50" than it would be if you have to repeat the entire calculation of production costs.
Also, this allows to use result of such calculation as a new value in UPDATE statement :
create table #tst(value int, suma int)
insert into #tst select 1, null
insert into #tst select 2, null
/*This does not work*/
update #tst
set suma = SUM(value)
from #tst
--Server: Msg 157, Level 15, State 1, Line 2
--An aggregate may not appear in the set list of an UPDATE statement.
/*This works*/
update #tst
set suma = Q.suma
from ( select sum(value) as suma
from #tst) as Q
--(2 row(s) affected)
March 7, 2006 at 3:50 pm
Well it looks you both of you guys have come up with the same solution!
Looks like a Winner to me!
Thanks to you both for your assistance. and thanks for taking the time with the explanation Joe. I appreciate it.
The things you learn! if I keep on learning things at this rate I'll be a DBA in no time!!! not bad a for a Novell engineer that has been "dumped" with web programming and DB design (for the apps).
I feel the need for a course! there is a lot of "stuff" to learn. And not so as to be able to get a job as a DBA either... just to be a better Web Application developer.
Thanks again to everyone for their help.
Gavin Baumanis
Smith and Wesson. The original point and click device.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply