use case result field problem
-
In my script, why is it just returning an error: "Invalid column name 'dte'."
create table #tmp (
id int,
x varchar(50) )
insert into #tmp values (1, 'a')
insert into #tmp values (2, 'b')
insert into #tmp values (3, 'c')
select case x
when 'a' then '1/1/2000'
else '1/2/2000'
end AS dte, month(dte), year(dte)
from #tmp
I hope someone can help, badly needed
AUXilliary COMmunication 1
-
As far as I know, you can only use a column alias (dte) in an "order by" clause. You need to duplicate the case statement in the month() and year(), or use a derived table.
--dup case statement.
select
case x
when 'a' then '1/1/2000'
else '1/2/2000'
end AS dte,
month(case x
when 'a' then '1/1/2000'
else '1/2/2000'
end),
year(case x
when 'a' then '1/1/2000'
else '1/2/2000'
end)
from #tmp
--derived table.
SELECT dte, month(dte), year(dte)
FROM (
select case x
when 'a' then '1/1/2000'
else '1/2/2000'
end AS dte
from #tmp
) d
-
thanks for the idea of derived table.
AUXilliary COMmunication 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply