November 17, 2004 at 8:32 pm
This is probably a trivial question, but I am stuck, so I would appreciate it if someone could help me:
Consder this SELECT statement -
SELECT a.abc , a.def, DATEDIFF(day,CAST(b.BeginDate),GetDate()) AS mydays FROM Assignment a LEFT OUTER JOIN BusinessObject b ON Assignment.boo_id = BusinessObject.boo_id WHERE mydays > 20
I am getting an Invalid Column mydays.
As you can see, mydays is a calculated column.
???
Thanks, J
November 17, 2004 at 9:53 pm
Your use of CAST is incomplete. Do you mean
cast(b.begindate as datetime) perhaps?
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2004 at 5:25 am
yes, sorry.
cast(b.begindate as daytime)
November 18, 2004 at 8:01 am
I am not sure sql * server allows you to use alias to calculated/normal columns in the where clause.
One alternative is to repeat the calculated column expression in the where clause also
SELECT a.abc , a.def, DATEDIFF(day,CAST(b.BeginDate),GetDate()) AS mydays FROM Assignment a LEFT OUTER JOIN BusinessObject b ON Assignment.boo_id = BusinessObject.boo_id
WHERE DATEDIFF(day,CAST(b.BeginDate,GetDate()) > 20
November 18, 2004 at 4:19 pm
Correct, it doesn't - would be damn useful though.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply