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 2008 Forums
 Transact-SQL (2008)
 nested case of any other solution

Author  Topic 

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-11-16 : 11:37:20

i have the following table

create table #data1
(
assetcenter int,
hped int,
model varchar(20)
)

-- INSERT DATA
insert into #data1 (assetcenter, hped,MODEL)
select 0,1,'MODEL DL3' union all
select 4,3 ,'MODEL R7' union all
select 0,null,NULL

-- result
select assetcenter, hped,MODEL,
case
when assetcenter = 0 and hped > 0 then hped
when assetcenter > hped then hped
when assetcenter = 0 and hped is null then 1
end as result
from #data1

from the above result i want to overwrite the resulr column

where model like '%DL3%' or model like '%R7%' RESULT SHOULD BE 2

FINAL OUTPUT

---------------------------

0 1 MODEL DL3 1
4 3 MODEL R7 2
0 NULL NULL 1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-16 : 11:44:30
I think the easiest way to do this, and perhaps the best, is just wrap it in another select statement

SELECT t1.assetcenter,t1.hped,t1.MODEL
,CASE WHEN t1.model like '%DL3%' or t1.model like '%R7%' THEN 2 ELSE t1.result END
FROM
(
select assetcenter, hped,MODEL,
case
when assetcenter = 0 and hped > 0 then hped
when assetcenter > hped then hped
when assetcenter = 0 and hped is null then 1
end as result
from #data1
) t1


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-11-16 : 11:51:28
Thanks Jim
Go to Top of Page

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-11-16 : 12:27:19
Hi jim,

Consider I am a result column which is empty. How will I update result colum using above select case.

Basically I want to update now instead of select.
Go to Top of Page
   

- Advertisement -