'Fields not GROUP BY creating multiple records instead of a single record

I am stuck and have been looking at this for too long. I have the below query that i want to return a single record showing sales price, cost price and subsequent charges but when I add in my case when statement these create multiple records. Looking for any assistance.

Current Output Current Output

Expected Output Expected Output

Current Query

SELECT DISTINCT
CONCAT(OBCUNO,'',OKCUNM) CUSTOMER,
OBORNO CO_NUMBER,
OBPONR CO_LINE,
OBPROJ PROJECT,
OBITNO ITEM,
OBHDPR M3_STYLE,
MMITDS CUST_STYLE,
MMFUDS DESCRIPTION,
OBORQA ORDER_QTY,
M9UCOS FACILITY_COST,
OBUCOS DISTRO_COST,

CASE WHEN OBUCOS = M9UCOS THEN 'MATCH' ELSE 'NO MATCH' END AS COST_PRC_MATCH,

OBSAPR SALE_PRC,

CASE WHEN O7CRID = 'PNP' THEN O7CRFA * OBORQA ELSE 0 END AS 'PNP CHG', 

CASE WHEN O7CRID = 'DTY' THEN O7CRAM * OBORQA ELSE 0 END AS 'DUTY CHG', 

OBVTCD VAT_CD,
SUM(CASE WHEN O7CRID = 'PNP' THEN OBSAPR * OBORQA + (CASE WHEN O7CRID = 'PNP' THEN OBORQA * O7CRFA ELSE 0 END) ELSE 0 END) AS TOTAL_LN_AMT
FROM MVXJDTA.OOLINE
    LEFT JOIN MVXJDTA.OOLICH 
        ON O7CONO = OBCONO
        AND O7ORNO = OBORNO
        AND O7PONR = OBPONR
    LEFT JOIN MVXJDTA.OCUSMA 
        ON OKCONO = OBCONO
        AND OKCUNO = OBCUNO
    LEFT JOIN MVXJDTA.MITMAS
        ON MMCONO = OBCONO
        AND MMITNO = OBITNO
    LEFT JOIN MVXJDTA.MITFAC
        ON M9CONO = OBCONO
        AND M9FACI = OBFACI
        AND M9ITNO = OBITNO
    LEFT JOIN MVXJDTA.CVATPC
        ON CVCONO = OBCONO
        AND CVDIVI = OBDIVI
        AND CVVTCD = OBVTCD
WHERE OBCONO = '610'
        AND OBORNO = '2000000190'
                
GROUP BY OBCUNO,OKCUNM,OBORNO,OBCUOR,OBPROJ,OBITNO,OBHDPR,MMITDS,MMFUDS,OBSAPR,M9UCOS,OBUCOS,OBVTCD,OBORQA,CVVTP1,O7CRFA,O7CRAM, O7CRID,OBPONR
sql


Solution 1:[1]

Your CASE statements are generating multiple values so you will get multiple rows in your result. E.g. pnp_chg has values of 0 and 168.4

Possibly you need to put SUM round each CASE statement - assuming that’s how you want to deal with this situation.

Solution 2:[2]

After trying and reviewing the output, using PIVOT function worked to provide a better result.

select * 
from 
(
  select 
  O7ORNO,
  O7CRID,
  O7CRAM

  from MVXJDTA.OOLICH 
) src
pivot
(
  sum(O7CRAM)
  for O7CRID in ([PNP], [DTY], [HAN])
) piv
 order by O7ORNO;

The issue I have now is the ISNULL and where to place it to remove the NULL values.

O7ORNO PNP DTY HAN
100000329 0.85 NULL NULL

I have tried to add the ISNULL into the sub query against O7CRAM but that did not remove the NULL values

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 NickW
Solution 2 KCS

Related Questions