CASE scenario - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

CASE scenario

Author

Topic

iceman_23
Starting Member

6 Posts

Posted - 2008-05-26 : 03:52:09

How can I be able to use "NewReceivedTime" as a variable?
Since having to create a new column using CASE statement in SQL would mean that user will not be able to use this new column name and having to receive error such as "Invalid Column Name: NewReceivedTime "

(case
when <value>
else <value>
end) as NewReceivedTime

I'm asking this because, I would want to use "NewReceivedTime" I've created to equate to another Time Column like
NewReceivedTime = LogDateTime something like that.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 03:56:42

Two ways

1. Either put the query containing th Case in a derived table

SELECT *
FROM
(SELECT otherfields,..,
case 
when <value>
else <value>
end as NewReceivedTime
FROM Table
Where....)t
JOIN Table2 t2
ON...
AND t2.column=t.NewReceivedTime
....
WHERE t.NewReceivedTime=<value>

or repeat case below

2.

SELECT otherfields,..,
case 
when <value>
else <value>
end as NewReceivedTime
FROM Table t
INNER JOIN Table2 t2
ON t2.column=case 
when <value>
else <value>
end 
Where....
and case 
when <value>
else <value>
end =<value>

I personally like the first method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources