Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sachingovekar
Posting Yak Master

101 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
2875 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
Posting Yak Master

101 Posts

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

sachingovekar
Posting Yak Master

101 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  
 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.16 seconds. Powered By: Snitz Forums 2000