How to work with this Case Statement
Hi,
I'm on DB2 UDB 9.7. Probably, this is a very easy one, but i can't seem to work this out.
I have the following Case Statement.
(Case WHEN VAR1 = 'Y' THEN 'TEXT1' ELSE '' END) ||','||
(CASE WHEN VAR2 = 'Y' THEN 'TEXT2' ELSE '' END) ||','||
(CASE WHEN VAR3 = 'Y' THEN 'TEXT3' ELSE '' END) ||','||
(CASE WHEN VAR4 = 'Y' THEN 'TEXT4' ELSE '' END) ||','||
(CASE WHEN VAR5 = 'Y' THEN 'TEXT5' ELSE '' END)
Expected Result : TEXT1,TEXT2,TEXT3,TEXT4,TE
For example, if only TEXT3 is there, then
Expected Result : Text3
When i do the above logic, i'm getting
,,,TEXT3,,
I don't want to show the commas if the value is not there. How to display that?
Please help.
I'm on DB2 UDB 9.7. Probably, this is a very easy one, but i can't seem to work this out.
I have the following Case Statement.
(Case WHEN VAR1 = 'Y' THEN 'TEXT1' ELSE '' END) ||','||
(CASE WHEN VAR2 = 'Y' THEN 'TEXT2' ELSE '' END) ||','||
(CASE WHEN VAR3 = 'Y' THEN 'TEXT3' ELSE '' END) ||','||
(CASE WHEN VAR4 = 'Y' THEN 'TEXT4' ELSE '' END) ||','||
(CASE WHEN VAR5 = 'Y' THEN 'TEXT5' ELSE '' END)
Expected Result : TEXT1,TEXT2,TEXT3,TEXT4,TE
For example, if only TEXT3 is there, then
Expected Result : Text3
When i do the above logic, i'm getting
,,,TEXT3,,
I don't want to show the commas if the value is not there. How to display that?
Please help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wouldn't {TRIM( BOTH...} be desirable? I'm not sure of the syntax for that version of DB2. There can be leading and trailing commas. Since commas are not inserted for non-'Y' values, I'm not sure how embedded commas could be duplicated.
Tom
Tom
Never mind that last comment about embedded commas. I was looking at Kent's example rather than the original. Kent's example apparently eliminates internal duplicates.
And I should have just edited that out of the comment, but I forgot that editing was available.
Tom
And I should have just edited that out of the comment, but I forgot that editing was available.
Tom
ASKER
Thank You both, so much!
There are several ways around this, all equally "yuck". One is to include the comma every time that you append a string. That does leave you with a possible leading or trailing comma, but at least you don't get what you're seeing. You can always filter the string to remove any leading comma.
You can also write a UDF to merge the strings, or use recursive SQL, but that does seem like overkill.
Kent