Referncing "as" named columns (?) in a selects where clause

Question

Post reply

Referncing "as" named columns (?) in a selects where clause

razmage

Old Hand

Points: 350
More actions
September 11, 2009 at 10:34 am

#226001

select 'a' as char1, 'b' as char2 where char1 != 'a'
In MySQL I was always able to do this however it doesn't appear to work with MS SQL. I've tried using where and having. Can I not used a "as" named column in a where statement? One of the selects that I'm working with needs to do some calculations in the columns being selected area and I'd hate to have to paste that whole calculation into the where statement as well.
I'm sure I'm just doing something wrong here, serves me right for trying to get something done before coffee has kicked in.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 1

See if this helps any...

http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html

razmage Old Hand Points: 350 More actions · Answer 2

Perfect. That answered my question. I wish it would allow the use of the alias in the where clause though but oh well 🙂 Thanks much!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

razmage (9/11/2009)
Perfect. That answered my question. I wish it would allow the use of the alias in the where clause though but oh well 🙂 Thanks much!

Agh... I wouldn't create a view for such a thing. Just create a subquery and use the criteria against that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

To illustrate Jeff's point:

SELECT SQ.char1, SQ.char2

FROM (

SELECT char1 = 'a',

char2 = 'b'

) SQ

WHERE SQ.char1 'a';

-- OR

;WITH SQ (char1, char2) AS (SELECT 'a', 'b')

SELECT SQ.char1, SQ.char2

FROM SQ

WHERE SQ.char1 'a';

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi