case statement - 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 2008 Forums

Transact-SQL (2008)

case statement

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-04-26 : 09:40:03

i am not sure how the syntax should be
i have 2 fields one contains a standard note string the other the value entered for the note string. i need to be able to say is

if field1 = a specific value and field2 text value field is null then a different field1 specific value and the pertaining text values for field2.

this is what i am starting with, but dont think its correct

case when (obsCatalog.Name ='PalCare_InitCons_Date_ft' and ISNULL(obser.ValueText, obsFS.Value))
then (obsCatalog.Name='PalCare_FU_Date_ft' and max(ISNULL(obser.ValueText, obsFS.Value)) end as test

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 10:00:42

A case stsatement returns a single value - it's doesn't update or assign values.
Maybe something like

testname = case when (obsCatalog.Name ='PalCare_InitCons_Date_ft' and obser.ValueText is null
then 'PalCare_FU_Date_ft' else obsCatalog.Name end ,
testvalue = case when (obsCatalog.Name ='PalCare_InitCons_Date_ft' and obser.ValueText is null
then obsFS.Value else obser.ValueText end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-04-26 : 10:46:22

the obsCatalog.Name is the note name field
the ISNULL(obser.ValueText, obsFS.Value) is the value of the note name
i need to say something more like this

INIT_DATE_OF_RQST = case when (obsCatalog.Name ='PalCare_InitCons_Date_ft' and ISNULL(obser.ValueText, obsFS.Value) is null)
then (ISNULL(obser.ValueText, obsFS.Value) and obsCatalog.Name='PalCare_FU_Date_ft') else ISNULL(obser.ValueText, obsFS.Value) end

if obsCatalog.Name ='PalCare_InitCons_Date_ft' and ISNULL(obser.ValueText, obsFS.Value) is null) then the ISNULL(obser.ValueText, obsFS.Value) where
obsCatalog.Name='PalCare_FU_Date_ft'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 10:58:16

>> then (ISNULL(obser.ValueText, obsFS.Value) and obsCatalog.Name='PalCare_FU_Date_ft')

this has to be a value - I'm not even sure what it's trying to do.
maybe
INIT_DATE_OF_RQST = case when obsCatalog.Name ='PalCare_InitCons_Date_ft' and ISNULL(obser.ValueText, obsFS.Value) is null
then 'PalCare_FU_Date_ft' else ISNULL(obser.ValueText, obsFS.Value) end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-26 : 14:23:40

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources