DB2 (mainframe DB2) – Select sql – CASE WHEN

My data is like this

SELECT * FROM EMP where EMP_ID=2713729

OUTPUT:

EMP_ID      CODE    AMOUNT
2713729     1A      1.00
2713729     2D      1.50

My requirement: If there are more than one CODE, then display CODE as ‘MULTI’, But if there is one CODE, then display that CODE itself (e.g. ‘1A’)

I want my output like below (if the data as above)

CODE AMOUNT MULTI 2.50

If my data is like this:

EMP_ID      CODE    AMOUNT
2713729     1A      1.00

then I want my output like below:

CODE        AMOUNT
1A          2.50

I tried with below SQL, but it throws error:

SELECT
    CASE 
        WHEN count(CODE) > 1 THEN 'MULTI'
        WHEN count(CODE) = 1 THEN CODE
    END as CODE,
SUM(AMT) FROM EMP where EMP_ID=2713729 group by EMP_ID

Error:

Error: DB2 SQL Error: SQLCODE=-122, SQLSTATE=42803, SQLERRMC=null, DRIVER=4.19.26
SQLState:  42803
ErrorCode: -122
Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=4.19.26
SQLState:  26501
ErrorCode: -514

If I hard the both ‘MULTI’ and CODE, then I am getting result.

SELECT
    CASE 
        WHEN count(CODE) > 1 THEN 'MULTI'
        WHEN count(CODE) = 1 THEN 'SINGLE'
    END as CODE,
SUM(AMT) FROM EMB where EMP_ID=2713729 group by EMP_ID

But I don’t want to hard the ‘SINGLE’

Answer

Try this. If you uncomment the commented out block, you may run this statement as is to check.

/*
WITH EMP (EMP_ID, CODE, AMOUNT) AS
(
          SELECT 2713729, '1A', 1.00 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 2713729, '2D', 1.50 FROM SYSIBM.SYSDUMMY1
)
*/
SELECT
  CASE 
    WHEN count (CODE) > 1 THEN 'MULTI'
    WHEN count (CODE) = 1 THEN MAX (CODE)
  END as CODE
, SUM (AMOUNT) AS AMOUNT
FROM EMP 
where EMP_ID = 2713729
group by EMP_ID