DB2 (mainframe DB2) - Select sql - CASE WHEN-CodePudding

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'

CodePudding user response：

Have you tried:

SELECT
    E1.EMP_ID,
    CASE WHEN ECNT.CODECNT > 1 THEN 'MULTI' ELSE CODE END as CODE,
    SUM(E1.AMOUNT) 
FROM EMP E1
     inner join
     (select EMP_ID, count(*) as CODECNT
      FROM EMP E2
      GROUP BY EMP_ID
     ) as ECNT
     on E1.EMP_ID=ECNT.EMP_ID
where E1.EMP_ID=2713729 
group by 
    E1.EMP_ID,
    CASE WHEN ECNT.CODECNT > 1 THEN 'MULTI' ELSE CODE END

Note: If the same EMP_ID has other rows with the same CODE it will marks them 'MULTI'. If this is not desirable, change count(*) to count(DISTINCT CODE).

or this:

SELECT 
      EMP_ID
    , CODE
    , SUM(AMOUNT)
FROM (
    SELECT
        E1.EMP_ID,
        CASE WHEN EXISTS (SELECT * FROM EMP E2 WHERE E2.EMP_ID=E1.EMP_ID and E2.CODE<>E1.CODE) THEN 'MULTI' ELSE CODE END as CODE,
        E1.AMOUNT
    FROM EMP E1
    ) Step1
where Step1.EMP_ID=2713729 
GROUP BY 
      EMP_ID
    , CODE

CodePudding user response：

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