DB2 Replacing string using CASE When

Question

My problem is that I concatenate the values ??of column col2 but in the When I have to replace them with the values ??in Then. For some reason my code doesn't work when I put this code inside my query, is there another way to do it? Would using Replace be good in this case? Could you give an example?

So my code is this one:

cte(col1, AliasCol) as
(Select col1,
    CASE
    WHEN Replace(Replace( LISTAGG(DISTINCT CONCAT( ', ', col2)) WITHIN GROUP 
        (ORDER BY CONCAT( ', ',col2)),',',''),' ','') = 'A' THEN 'Z'
    WHEN Replace(Replace( LISTAGG(DISTINCT CONCAT( ', ', col2)) WITHIN GROUP 
        (ORDER BY CONCAT( ', ', col2)),',',''),' ','') = 'B' THEN 'Y'    
    WHEN Replace(Replace( LISTAGG(DISTINCT CONCAT( ', ', col2)) WITHIN GROUP 
        (ORDER BY CONCAT( ', ', col2)),',',''),' ','') = 'C' THEN 'X'    
    END 
    FROM Table
    Where 
    col2 IN ('A','B','C','D')
    and col >= (select TodaysDate from Date)
    GROUP By col1
)

My output should be: ZYXD

Edit:

My col2 column displays all codes like this:

A
B
C
D

After I concat them to this ABCD the value of ABCD should be replace by ZYXD. Replacing 'A' by 'Z', 'Y' by 'B' and 'C' by X'.

Answer 1

Try this:

WITH MYTAB(COL1, COL2) AS
(
VALUES
  (1, 'A')
, (1, 'A')
, (1, 'B')
, (1, 'C')
, (1, 'D')
)
SELECT 
  COL1
, TRANSLATE(LISTAGG(DISTINCT COL2) WITHIN GROUP (ORDER BY COL2), 'ZYX', 'ABC') MY_OUT 
FROM MYTAB
GROUP BY COL1;