CODEWITHSUNDEEP
sqlcase
KCS
KCS

Reputation: 59

CASE WHEN creating duplicate rows

I have a table i need to get data from and need to incorporate adding a CASE WHEN to add data to columns. I have added CASE WHEN and GROUP BY but cannot get the desired output. I have looked at other similar questions but the minimal solutions I have seen, none have helped in resolving the issue.

Appreciate any input.

Below is the current query.

SELECT
AHCONO  'Company',
AHRIDN  'Order',
AHRIDL  'Order Line',
AHITNO  'Item',
CASE WHEN AHATID = 'DROP' THEN  AHATAV END AS 'DROP',
CASE WHEN AHATID = 'SEASON' THEN AHATAV END AS 'SEASON'
FROM MVXJDTA.MOATTR
WHERE AHCONO = '650'
    AND AHRIDN = '2000000059' 
    AND AHRIDL = '1'
    AND AHATID IN ('SEASON','DROP')
GROUP BY AHCONO,AHRIDN, AHRIDL, AHITNO, AHATID, AHATAV

Current Output

Company Order Order Line Item DROP SEASON
650 2000000059 1 1000019-0006 DROP01 'NULL'
650 2000000059 1 1000019-0006 'NULL' NOV 21

Desired Output

Company Order Order Line Item DROP SEASON
650 2000000059 1 1000019-0006 DROP01 NOV 21

Upvotes: 1

Views: 1667

Answers (1)

Stu
Stu

Reputation: 32609

You can aggregate the case expressions to eliminate the NULL values, try:

SELECT
AHCONO  "Company",
AHRIDN  "Order",
AHRIDL  "Order Line",
AHITNO  "Item",
MAX(CASE WHEN AHATID = 'DROP' THEN  AHATAV END) "DROP",
MAX(CASE WHEN AHATID = 'SEASON' THEN AHATAV END) "SEASON"
FROM MVXJDTA.MOATTR
WHERE AHCONO = '650'
    AND AHRIDN = '2000000059' 
    AND AHRIDL = '1'
    AND AHATID IN ('SEASON','DROP')
GROUP BY AHCONO, AHRIDN, AHRIDL, AHITNO

Also note that literal values use single quotes and should not be used for column aliases, use double quotes as delimiters.

Upvotes: 2

Related Questions