How to work with this Case Statement

Posted on 2012-09-14
Medium Priority
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
  • 3
  • 2
LVL 46

Expert Comment

by:Kent Olsen
ID: 38399809
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 27

Assisted Solution

tliotta earned 400 total points
ID: 38401284
Why not just TRIM() leading/trailing commas from the entire expression?

LVL 46

Accepted Solution

Kent Olsen earned 1600 total points
ID: 38401705
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.
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 27

Expert Comment

ID: 38402413
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 27

Expert Comment

ID: 38402417
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

ID: 38404229
Thank You both, so much!

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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