How to work with this Case Statement


 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) ||','||

Expected Result : TEXT1,TEXT2,TEXT3,TEXT4,TEXT5

For example, if only TEXT3 is there, then
Expected Result : Text3

When i do the above logic, i'm getting

I don't want to show the commas if the value is not there. How to display that?

Please help.
Who is Participating?
Kent OlsenData Warehouse Architect / DBACommented:
Hi Tom,

Mostly because I've been brain dead all week.  And there's a good chance that I'll be brain dead much of next week, too.  But I am holding out hope for the following week.  :)

Hi pvbandi,

Tom's dead right.  Pass the entire concatenated string through the TRIM function to drop the leading/trailing commas.  You'll also want to rework the string generation just a bit as the current SQL could put consecutive commas in the middle of the string.  e.g.  If var1 and var4 are both 'Y'.

  (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)

Open in new window

I'm assuming that 'TEXT1' .. 'TEXT5' could be variables so this example is written like it is.
Kent OlsenData Warehouse Architect / DBACommented:
Hi Pvsbandi,

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.

Why not just TRIM() leading/trailing commas from the entire expression?

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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.

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.

pvsbandiAuthor Commented:
Thank You both, so much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.