Using field aliases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using field aliases

Author

Topic

figmo
Starting Member

18 Posts

Posted - 2010-10-05 : 13:38:38

How come I can do this:
SELECT (Field1 + Field2) AS Blah
FROM Table
ORDER BY Blah

But I cannot do this:
SELECT (Field1 + Field2) AS Blah
FROM Table
WHERE Blah LIKE 'Something%'
ORDER BY Blah

So the field alias 'Blah' works in the ORDER BY clause, but not if I try to use it in a WHERE clause.

I want to create output that is a single column concatenating 2 fields, and search for 'Something%' in either column.

I know I could just use:
WHERE Field1 LIKE 'Something%' OR Field2 LIKE 'Something%'

But I'm curious if there is just a different way I need to reference the alias to make it work. Plus, it sure would be prettier to just have the one LIKE

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-05 : 13:43:34

Its because the ORDER BY is the very last thing considered by the optimizer. All expressions have been resolved by the time the result set is ordered. That is not necessarily true for the WHERE clause.

EDIT:
as Sachin says you should use the exression rather than the alias in the ORDER BY as well.

Be One with the Optimizer
TG

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-10-05 : 13:46:28

Yes, you can order by an alias or int representation of the column order for ex,

(order by 1)

To mimic what you want just

where (Field1 + Field2) like ...

[url]http://msdn.microsoft.com/en-us/library/ms188385.aspx[/url]

edit:added BOL link

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 13:49:12

quote:

EDIT:
as Sachin says you should use the exression rather than the alias in the ORDER BY as well

Sorry deleted my post.Thought your answer is more appropriate.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 13:49:35

you can do this though


SELECT columns...
FROM
(
SELECT (Field1 + Field2) AS Blah,...
FROM Table
)t
WHERE Blah LIKE 'Something%'
ORDER BY Blah

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-10-05 : 13:51:37

What is the advantage of using the expression in Order By rather than alias?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-05 : 13:57:47

quote:
Originally posted by nathans

What is the advantage of using the expression in Order By rather than alias?

IMO:
Just removes a layer of obfuscation. And it is little things like that that can throw errors in future versions of sql server making upgrades more difficult.

Be One with the Optimizer
TG

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 13:58:39

quote:
Originally posted by TG

you should use the exression rather than the alias in the ORDER BY as well.

why?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-05 : 14:00:43

quote:
Originally posted by russell

quote:
Originally posted by TG

you should use the exression rather than the alias in the ORDER BY as well.

why?

see my previous answer.
I don't know if that is best practice or not it's just my opinion.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources