select command invalid column name

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

SQL Server 2000 Forums

SQL Server Development (2000)

select command invalid column name

Author

Topic

matrixr
Starting Member

26 Posts

Posted - 2006-06-07 : 23:54:25

i have this query
select
datediff(dd,getdate(),getdate()+3) as Diff, -- getdate()+3 is an example
case when (Diff < 7) then 1
when (Diff >=7 and Diff < 14) then 2
when (Diff >=14 and Diff < 21) then 3
when (Diff >=21 and Diff < 28) then 4
end as WeekNumber

i get this error Invalid column name 'Diff'.

how do i calculate diff then use it within the same query without doing declare @diff int ...etc?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 00:10:46

you can't use the column alias in the select statement except in the ORDER BY.

I changed the select datediff .. as Diff as a derived table


select Diff,
       case when (Diff < 7) then 1
            when (Diff >=7 and Diff < 14) then 2
            when (Diff >=14 and Diff < 21) then 3
            when (Diff >=21 and Diff < 28) then 4
            end as WeekNumber
from
(
    select datediff(dd,getdate(),getdate()+3) as Diff -- getdate()+3 is an example
) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources