GOT BRAIN FREEZE !!!

[NevG]

Hi gang

Cant code my way out of a paper bag today ....

Can someone tell me how to query an aliased column?

select col as new_colname from table
where new_colname = [value]

thanks guys and gals..

duh !!

 

[john76]

I don't believe you can use the aliased name in the same query, so you'll have to use:

Select col As new_colname
  From table
 Where col = [value]

Or something like that...

HTH,
John

 

[SQLBill]

Can't do it. Refer to the BOL, use the Index tab, enter SELECT. Scroll down to Select (Described). Click on that, choose the option for Transact-SQL, in the right pane scroll down to the Arguments section under Select Clause. You'll find column_alias. It says it can be used in the SELECT and ORDER BY, but NOT in WHERE, GROUP BY or HAVING.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's Help
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[ESquared]

>NOT in WHERE, GROUP BY or HAVING

Ant that can be quite a pain sometimes, especially for extremely complex calculated expressions. I can understand it not being in the WHERE clause or perhaps even the GROUP BY clause, but why not in the HAVING clause? That's performed after everything else, isn't it?

 

[SQLBill]

I don't make the rules....Microsoft MUST have some reason for designing it this way. But heck if I can figure it out. An alias should be usable anywhere...that's why you alias it don't you?

Warning! Here comes SQLBill's opinion:
I think the column alias was originally meant as a HEADER only. Let's say you have a column MyNumbers. If you run a query to return that column, the HEADER (the label,whatever you call it at the top of the column) will read MyNumbers. However, if you do AVG(MyNumbers), the header will read AVG(MyNumbers). Then if you get into CONVERT, etc the header can get really unreadable. So I believe MS created the column alias to allow us to create more readable/understandable headers. We just took that and saw a better use for it (as a true alias) and expect it to work. Maybe MS can eventually make it work in a future version. (But don't hold your breath waiting for it.)

-SQLBill

 

[Pattycake245]

I've done it this way, but it may be more code than you'll want to use. Would be beneficial for large queries with a bunch of computed columns or columns derived from case statements:

Select new_colname
from
(select col As new_colname
From table) as results
Where new_colname = [value]

I use this for a large query for a bunch of computed columns, etc that I alias, better than writing out all the computations.

Tim

 

[ESquared]

Good point, Tim... thanks for the reminder. It IS a good way.

There was another thread that mentioned this same concept of encapsulating a query to avoid recomputation of an expression. However, no one confirmed whether it was any more efficient.

Do you think the query optimizer is smart enough to not reevaluate the same expression in a query when it is given once in the SELECT clause and once in the WHERE clause (for example)?

 

[Pattycake245]

When I've used this syntax in some of my queries and it runs a little faster. I'm sure we are both thinking of the same thread, which was where I found the idea. I'll see if I can find it.

Tim

 

[SQLBill]

Tim,

How about making your post a FAQ? If you find the other post, you can combine or include that information with yours (if it adds anything) and give credit to the originator.

-SQLBill

 

[ESquared]

For the life of me I can't find that other thread. Anyway, I was the originator who suggested encapsulating very complex expressions as a derived table to avoid having them evaluated multiple times.