| Author |
 Topic  |
|
|
sachingovekar
Yak Posting Veteran
97 Posts |
 Posted - 11/16/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/16/2012 : 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 |
 |
|
|
sachingovekar
Yak Posting Veteran
97 Posts |
Posted - 11/16/2012 : 11:51:28
|
| Thanks Jim |
|
|
|
sachingovekar
Yak Posting Veteran
97 Posts |
Posted - 11/16/2012 : 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. |
|
|
| |
Topic |
|
|
|
nested case of any other solution
