Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query which parses a string values from a column:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1 

When I do:

SELECT left(DateTimeSent, 17) AS timesent FROM allog_07_11_e query1.timesent != '0'

I get:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'timesent'.

Any ideas why?

share|improve this question
3  
As to why you can't use in WHERE clause an alias defined in the SELECT list, it's due to the: order of execution of a SELECT statement –  ypercube May 16 '12 at 13:27
add comment

2 Answers

You can't reference an alias in the WHERE clause. You'd have to do something like this:

select left(DateTimeSent, 17) as timesent
from allog_07_11_e
where left(DateTimeSent, 17) <> '0'

Likewise, you could also use a subquery:

select *
from
(
    select left(DateTimeSent, 17) as timesent
    from allog_07_11_e
) a
where timesent <> '0'
share|improve this answer
    
You can't reference an alias in the WHERE clause - Not entirely true - see my answer. –  JNK May 16 '12 at 13:22
add comment

You CAN reference an alias if you define it with CROSS APPLY:

SELECT timesent 
FROM allog_07_11_e query1
CROSS APPLY(SELECT left(DateTimeSent, 17)) CxA(TimeSent)
WHERE timesent <> '0'
share|improve this answer
    
Touche, my friend. +1 for the corner-case. I don't see this as a viable option for the OP, but nevertheless you did find a way! –  Thomas Stringer May 16 '12 at 13:28
    
@Shark I only really use this when I need to derive other calcs from calcs. It really simplfies say making a calculation based off another average or something. –  JNK May 16 '12 at 13:42
    
Nah. You are cheating :). The cross apply is sub-query. It is just a bit different from the one posted by @Shark. –  Mikael Eriksson May 16 '12 at 13:55
    
@MikaelEriksson I wouldn't count it as a subquery since there's no FROM in it! –  JNK May 16 '12 at 13:57
    
Ahh. Yes, I see that this could turn into some kind of philosophical discussion. For me it is the presence of the SELECT that makes it a query. –  Mikael Eriksson May 16 '12 at 14:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.