Best Choice to use Case
-- This is one of the best examples to use CASE. In this case we have three
-- strings that need to be concatenated with a comma. This will be complex 
-- using DECODE function since three columns have to be compared.

WITH q_tab AS
 (SELECT 1 idx, '1aa' c1, NULL  c2, '1cc' c3 FROM DUAL UNION
  SELECT 2 idx, NULL  c1, '2bb' c2, '2cc' c3 FROM DUAL UNION
  SELECT 3 idx, '3aa' c1, '3bb' c2, NULL  c3 FROM DUAL UNION
  SELECT 4 idx, '4aa' c1, '4bb' c2, '4cc' c3 FROM DUAL UNION
  SELECT 5 idx, NULL  c1, NULL  c2, NULL  c3 FROM DUAL)
SELECT c1, c2, c3,
  CASE
    WHEN c1 IS NULL AND c2 IS NULL AND c3 IS NULL 
      THEN NULL
    WHEN c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NULL
      THEN c1||','||c2
    WHEN c1 IS NULL AND c2 IS NOT NULL AND c3 IS NOT NULL
      THEN c2||','||c3
    WHEN c1 IS NOT NULL AND c2 IS NULL AND c3 IS NOT NULL
      THEN c1||','||c3
    ELSE  C1||','||c2||','||C3
  END concat_string
FROM q_tab;

CASE Function Analysis
Analysis - Case Vs. Decode

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 4th, 2015

  1146