How to work with this Case Statement

Posted on 2012-09-14
Last Modified: 2012-09-16

 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.
Question by:pvsbandi
    LVL 45

    Expert Comment

    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.

    LVL 26

    Assisted Solution

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

    LVL 45

    Accepted Solution

    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.
    LVL 26

    Expert Comment

    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.

    LVL 26

    Expert Comment

    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.


    Author Closing Comment

    Thank You both, so much!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now