CODEWITHSUNDEEP
sqldatabasecountdb2case
Chicken_Hawk
Chicken_Hawk

Reputation: 71

SQL case statement that returns query based on instances of a row

I am trying to write a sub query that counts up the number of 'TRACE_NO' and if there is only one instance I want it to display the original string. However, if it has more than one 'TRACE_NO' instance I want the row to display 'Duplicate'. What am I doing wrong? 

SELECT 
    SL.FILE_NO AS FILE,
    (SELECT CASE(WHEN TR.TRACE_NO HAVING COUNT(*) >1 THEN 'DUPLICATE' ELSE
TR.TRACE_NO) 
     FROM IASDB.TRACE_DETAIL TR 
     WHERE SL.INVOICE_NO = TR.INVOICE_NO
       AND TR.TRACE_TYPE IN 'XX') AS XX

TABLE

FILE   XX 
1120 | SEA
1315 | SDF
1251 | SEA
1251 | GEG
1991 | ATL
1235 | BOI
1235 | CVG

Desired Output:

FILE | XX
1120 | SEA
1315 | SDF
1251 | DUPLICATE
1991 | ATL
1235 | DUPLICATE

Upvotes: 0

Views: 117

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Assuming this is part of a larger query, this may be the logic you want:

SELECT SL.FILE_NO AS FILE,
       (SELECT CASE WHEN COUNT(*) > 1 THEN 'DUPLICATE' ELSE MIN(TR.TRACE_NO) END
        FROM IASDB.TRACE_DETAIL TR 
        WHERE SL.INVOICE_NO = TR.INVOICE_NO AND TR.TRACE_TYPE IN 'XX'
       ) AS XX

Upvotes: 1

Bulat
Bulat

Reputation: 6969

Try to implement this with your logic in the wrapping query like this:

SELECT 
  FILE, 
  CASE 
    WHEN CNT = 1 THEN TRACE_NO
    ELSE 'DUPLICATE'
  END AS XX
FROM (
  SELECT SL.FILE_NO AS FILE, TR.TRACE_NO, COUNT(*) as CNT
  FROM IASDB.TRACE_DETAIL TR 
  GROUP BY  SL.FILE_NO AS, TR.TRACE_NO
)

Upvotes: 0

Related Questions