SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 nested case of any other solution
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 11/16/2012 :  11:37:20  Show Profile  Reply with Quote

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
2870 Posts

Posted - 11/16/2012 :  11:44:30  Show Profile  Reply with Quote
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
Yak Posting Veteran

99 Posts

Posted - 11/16/2012 :  11:51:28  Show Profile  Reply with Quote
Thanks Jim
Go to Top of Page

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 11/16/2012 :  12:27:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000