Case When Syntax Error - 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 2005 Forums

Transact-SQL (2005)

Case When Syntax Error

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-14 : 12:14:54

I am using this code to form a list of values. I keep getting an error that says syntax error near as and it brings me to the last line of code. Can anyone see what is wrong with my syntax?

coalesce(i.chk_wound_02, '') +
case when i.chk_wound_03 is null then ''
else ',' + i.chk_wound_03 + '' +
case when i.chk_wound_04 is null then ''
else ',' + i.chk_wound_04
end
end as 'RiskFactors',

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-14 : 12:31:43

I tried something like this too but more syntax errors with the parenthesis. Any ideas?

coalesce (i.chk_wound_01 + '' + i.chk_wound_02 + '' + i.chk_wound_03 + '' +
i.chk_wound_04 + '' + i.chk_wound_05 + '' + i.chk_wound_06 + '' +
i.chk_wound_07 + '' + i.chk_wound_08 + '' + i.chk_wound_09 + '' +
i.chk_wound_09 + '' + i.chk_wound_10 + '' + i.chk_wound_10 + '' +
i.chk_wound_11 + '' + i.txt_wound_other) as 'RiskFactors'

quote:
Originally posted by sross81

I am using this code to form a list of values. I keep getting an error that says syntax error near as and it brings me to the last line of code. Can anyone see what is wrong with my syntax?

coalesce(i.chk_wound_02, '') +
case when i.chk_wound_03 is null then ''
else ',' + i.chk_wound_03 + '' +
case when i.chk_wound_04 is null then ''
else ',' + i.chk_wound_04
end
end as 'RiskFactors',

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-14 : 12:31:48

coalesce(i.chk_wound_02, '') +
case when i.chk_wound_03 is null then ''
else ',' + i.chk_wound_03 + '' end +
case when i.chk_wound_04 is null then ''
else ',' + i.chk_wound_04
end
end as 'RiskFactors',

add the red, remove the blue.. I think.

An infinite universe is the ultimate cartesian product.

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-14 : 12:37:29

That works. Thanks :)

quote:
Originally posted by cat_jesus

coalesce(i.chk_wound_02, '') +
case when i.chk_wound_03 is null then ''
else ',' + i.chk_wound_03 + '' end +
case when i.chk_wound_04 is null then ''
else ',' + i.chk_wound_04
end
end as 'RiskFactors',

add the red, remove the blue.. I think.

An infinite universe is the ultimate cartesian product.

Thanks in Advance!
Sherri

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-14 : 12:38:00

Try this

select
coalesce(i.chk_wound_01 + ',','',)
+ coalesce(i.chk_wound_02 + ',','',)
+ coalesce(i.chk_wound_03 + ',','',)
+ coalesce(i.chk_wound_04 + ',','',)
+ coalesce(i.chk_wound_05 + ',','',)
+ coalesce(i.chk_wound_06 + ',','',)
+ coalesce(i.chk_wound_07 + ',','',)
+ coalesce(i.chk_wound_08 + ',','',)
as RiskFactors

An infinite universe is the ultimate cartesian product.

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-14 : 13:48:34

This coalesce code works well.

I just have an issue with my database in that most of the time if nothing is in a field it says NULL but for some reason sometimes a '0' gets in there. Is there a way to also filter out the 0's with coalesce?

quote:
Originally posted by cat_jesus

Try this

select
coalesce(i.chk_wound_01 + ',','',)
+ coalesce(i.chk_wound_02 + ',','',)
+ coalesce(i.chk_wound_03 + ',','',)
+ coalesce(i.chk_wound_04 + ',','',)
+ coalesce(i.chk_wound_05 + ',','',)
+ coalesce(i.chk_wound_06 + ',','',)
+ coalesce(i.chk_wound_07 + ',','',)
+ coalesce(i.chk_wound_08 + ',','',)
as RiskFactors

An infinite universe is the ultimate cartesian product.

Thanks in Advance!
Sherri

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-14 : 15:02:54

sure

Try this out.

declare @temp AS VARCHAR(10)
declare @temp1 AS VARCHAR(10)
declare @temp2 AS VARCHAR(10)

SET @temp = '0'
SET @temp1 = null
SET @temp2 = 'something'

SELECT COALESCE(NULLIF(@temp,'0') + ',','') + COALESCE(NULLIF(@temp1,'0')+ ',','') + COALESCE(NULLIF(@temp2,'0')+ ',','')

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources