4

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?

5

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'
  • You can't reference an alias in the WHERE clause - Not entirely true - see my answer. – JNK May 16 '12 at 13:22
2

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'
  • 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
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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