Make distinct to specific sql (db2) columns-CodePudding

I originally had this sql query

WITH FACTURA AS (

SELECT 
  DISTINCT SIH.IHDOCN FACTURA, RCM.CNME NOMCLI, SIH.SIINVD FECHA

  FROM
       $P!{AMB}.SIH SIH 
JOIN  $P!{AMB}.RCM RCM ON RCM.CCUST = SIH.SICUST

  WHERE SUBSTR(SIH.SIINVD,1,6) >= CASE WHEN ('$P!{FECHA_ANO_MES}' = '' OR '$P!{FECHA_ANO_MES}' = 'null')
                                       THEN 0
                                       ELSE CASE WHEN LENGTH('$P!{FECHA_ANO_MES}') = 10
                                                 THEN REPLACE(SUBSTR('$P!{FECHA_ANO_MES}', 1, 7), '-', '')
                                                 ELSE '$P!{FECHA_ANO_MES}' END END
  AND SIH.IHDOCN LIKE CASE WHEN ('$P!{FAC}' = '' OR '$P!{FAC}' = 'null')
                           THEN ('%')
                           ELSE '$P!{FAC}%' END
  AND SIH.IHDPFX = 'EX'
  AND  IHDTYP = '1'  
)

SELECT FAC.FACTURA, 
FAC.NOMCLI NOMCLI,
SUM(CASE WHEN OG.GRUPO = 'OLMALM' THEN LES.ESAMT ELSE 0 END) ALMACENAJE,
SUM(CASE WHEN OG.GRUPO = 'OLMCARGUE' THEN LES.ESAMT ELSE 0 END) CARGUE,
SUM(CASE WHEN OG.GRUPO = 'OLMCARTAGE' THEN LES.ESAMT ELSE 0 END) OLMCARTAGE,
SUM(CASE WHEN OG.GRUPO = 'OLMCOTECNA' THEN LES.ESAMT ELSE 0 END) COTECNA,
SUM(CASE WHEN OG.GRUPO = 'OLMCUTTOFF' THEN LES.ESAMT ELSE 0 END) CUTTOFF,
SUM(CASE WHEN OG.GRUPO = 'OLMDOCTRA' THEN LES.ESAMT ELSE 0 END) OLMDOCFEE,
SUM(CASE WHEN OG.GRUPO  = 'OLMICA' THEN LES.ESAMT ELSE 0 END) ICA,
SUM(CASE WHEN OG.GRUPO = 'OLMLLENA' THEN LES.ESAMT ELSE 0 END) LLENACO,
SUM(CASE WHEN OG.GRUPO  = 'OLMMOVINSP' THEN LES.ESAMT ELSE 0 END) MOVINSP,
SUM(CASE WHEN OG.GRUPO = 'OLMOPEPOR' THEN LES.ESAMT ELSE 0 END) OPEPORT,
SUM(CASE WHEN OG.GRUPO = 'OLMREFR' THEN LES.ESAMT ELSE 0 END) REFRI,
SUM(CASE WHEN OG.GRUPO = 'SELLO' THEN LES.ESAMT ELSE 0 END) SELLO,
SUM(CASE WHEN OG.GRUPO = 'OLMSIA' THEN LES.ESAMT ELSE 0 END) SIA,
SUM(CASE WHEN OG.GRUPO = 'OLMSORTING' THEN LES.ESAMT ELSE 0 END) SORTING,
SUM(CASE WHEN OG.GRUPO = 'OLMSTANDBY' THEN LES.ESAMT ELSE 0 END) OLMSTANDBY,
SUM(CASE WHEN OG.GRUPO = 'OLMTRAMAR' THEN LES.ESAMT ELSE 0 END) TRAMAR,
MAX(CASE WHEN OG.GRUPO = 'OLMTRAMAR' THEN LES.ESCURR ELSE '' END) MNDTRAMAR,
SUM(CASE WHEN OG.GRUPO = 'OLMTRAEREO' THEN LES.ESAMT ELSE 0 END) TRAEREO,
SUM(CASE WHEN OG.GRUPO = 'OLMTR' THEN LES.ESAMT ELSE 0 END) TRASTER,
SUM(CASE WHEN OG.GRUPO = 'OLMUSOINST' THEN LES.ESAMT ELSE 0 END) OLMUSOINST,
SUM(CASE WHEN OG.GRUPO = 'OLMPESAJE' THEN LES.ESAMT ELSE 0 END) OLMPESAJE,
SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN' THEN LES.ESAMT ELSE 0 END) SERV_LOGISTICO,
SUM(CASE WHEN OG.GRUPO = 'OLMCANCEL' THEN LES.ESAMT ELSE 0 END) OLMCANCEL,
SUM(CASE WHEN OG.GRUPO = 'OLMCOMODAT' THEN LES.ESAMT ELSE 0 END) OLMCOMODAT,
SUM(CASE WHEN OG.GRUPO = 'OLMCONTEN' THEN LES.ESAMT ELSE 0 END) OLMCONTEN,
SUM(CASE WHEN OG.GRUPO = 'OLMNOINT' THEN LES.ESAMT ELSE 0 END) OLMNOINT,
SUM(CASE WHEN OG.GRUPO = 'OLMSERD' THEN LES.ESAMT ELSE 0 END)  OLMSERD,
SUM(CASE WHEN OG.GRUPO = 'OLMTTERDEV' THEN LES.ESAMT ELSE 0 END) OLMTTERDEV,
SUM(CASE WHEN OG.GRUPO = 'OLMCOURIER' THEN LES.ESAMT ELSE 0 END) OLMCOURIER,
SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN' THEN LES.ESAMT ELSE 0 END) OLMSELLEN1,
SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN2' THEN LES.ESAMT ELSE 0 END) OLMSELLEN2,
FAC.FECHA
FROM  FACTURA  FAC
LEFT  OUTER JOIN $P!{AMB}.LLH LLH ON FAC.FACTURA = LLH.LHCINV
LEFT OUTER JOIN $P!{AMB}.LES LES ON LLH.LHLOAD = LES.ESLOAD AND LLH.LHORDN = LES.ESORDN
LEFT OUTER JOIN ERPLXFU.VW_OLM_GRUPOS OG ON LES.ESSCCD = OG.COD_OLM
GROUP BY FAC.FACTURA, FAC.NOMCLI, FAC.FECHA
ORDER BY FAC.FACTURA

However, the program I use does not recognize that way of doing queries, so I transformed it

SELECT SIH.IHDOCN FACTURA, RCM.CNME NOMCLI, 
    
    SUM(CASE WHEN OG.GRUPO = 'OLMALM' THEN LES.ESAMT ELSE 0 END) ALMACENAJE,
    SUM(CASE WHEN OG.GRUPO = 'OLMCARGUE' THEN LES.ESAMT ELSE 0 END) CARGUE,
    SUM(CASE WHEN OG.GRUPO = 'OLMCARTAGE' THEN LES.ESAMT ELSE 0 END) OLMCARTAGE,
    SUM(CASE WHEN OG.GRUPO = 'OLMCOTECNA' THEN LES.ESAMT ELSE 0 END) COTECNA,
    SUM(CASE WHEN OG.GRUPO = 'OLMCUTTOFF' THEN LES.ESAMT ELSE 0 END) CUTTOFF,
    SUM(CASE WHEN OG.GRUPO = 'OLMDOCTRA' THEN LES.ESAMT ELSE 0 END) OLMDOCFEE,
    SUM(CASE WHEN OG.GRUPO  = 'OLMICA' THEN LES.ESAMT ELSE 0 END) ICA,
    SUM(CASE WHEN OG.GRUPO = 'OLMLLENA' THEN LES.ESAMT ELSE 0 END) LLENACO,
    SUM(CASE WHEN OG.GRUPO  = 'OLMMOVINSP' THEN LES.ESAMT ELSE 0 END) MOVINSP,
    SUM(CASE WHEN OG.GRUPO = 'OLMOPEPOR' THEN LES.ESAMT ELSE 0 END) OPEPORT,
    SUM(CASE WHEN OG.GRUPO = 'OLMREFR' THEN LES.ESAMT ELSE 0 END) REFRI,
    SUM(CASE WHEN OG.GRUPO = 'SELLO' THEN LES.ESAMT ELSE 0 END) SELLO,
    SUM(CASE WHEN OG.GRUPO = 'OLMSIA' THEN LES.ESAMT ELSE 0 END) SIA,
    SUM(CASE WHEN OG.GRUPO = 'OLMSORTING' THEN LES.ESAMT ELSE 0 END) SORTING,
    SUM(CASE WHEN OG.GRUPO = 'OLMSTANDBY' THEN LES.ESAMT ELSE 0 END) OLMSTANDBY,
    SUM(CASE WHEN OG.GRUPO = 'OLMTRAMAR' THEN LES.ESAMT ELSE 0 END) TRAMAR,
    MAX(CASE WHEN OG.GRUPO = 'OLMTRAMAR' THEN LES.ESCURR ELSE '' END) MNDTRAMAR,
    SUM(CASE WHEN OG.GRUPO = 'OLMTRAEREO' THEN LES.ESAMT ELSE 0 END) TRAEREO,
    SUM(CASE WHEN OG.GRUPO = 'OLMTR' THEN LES.ESAMT ELSE 0 END) TRASTER,
    SUM(CASE WHEN OG.GRUPO = 'OLMUSOINST' THEN LES.ESAMT ELSE 0 END) OLMUSOINST,
    SUM(CASE WHEN OG.GRUPO = 'OLMPESAJE' THEN LES.ESAMT ELSE 0 END) OLMPESAJE,
    SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN' THEN LES.ESAMT ELSE 0 END) SERV_LOGISTICO,
    SUM(CASE WHEN OG.GRUPO = 'OLMCANCEL' THEN LES.ESAMT ELSE 0 END) OLMCANCEL,
    SUM(CASE WHEN OG.GRUPO = 'OLMCOMODAT' THEN LES.ESAMT ELSE 0 END) OLMCOMODAT,
    SUM(CASE WHEN OG.GRUPO = 'OLMCONTEN' THEN LES.ESAMT ELSE 0 END) OLMCONTEN,
    SUM(CASE WHEN OG.GRUPO = 'OLMNOINT' THEN LES.ESAMT ELSE 0 END) OLMNOINT,
    SUM(CASE WHEN OG.GRUPO = 'OLMSERD' THEN LES.ESAMT ELSE 0 END)  OLMSERD,
    SUM(CASE WHEN OG.GRUPO = 'OLMTTERDEV' THEN LES.ESAMT ELSE 0 END) OLMTTERDEV,
    SUM(CASE WHEN OG.GRUPO = 'OLMCOURIER' THEN LES.ESAMT ELSE 0 END) OLMCOURIER,
    SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN' THEN LES.ESAMT ELSE 0 END) OLMSELLEN1,
    SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN2' THEN LES.ESAMT ELSE 0 END) OLMSELLEN2,
    
    SIH.SIINVD FECHA
    
    FROM  $P!{AMB}.RCM RCM
    JOIN  $P!{AMB}.SIH SIH  ON RCM.CCUST = SIH.SICUST
    LEFT  OUTER JOIN $P!{AMB}.LLH LLH ON SIH.IHDOCN = LLH.LHCINV
    LEFT OUTER JOIN $P!{AMB}.LES LES ON LLH.LHLOAD = LES.ESLOAD AND LLH.LHORDN = LES.ESORDN
    LEFT OUTER JOIN ERPLXFU.VW_OLM_GRUPOS OG ON LES.ESSCCD = OG.COD_OLM
    WHERE SUBSTR(SIH.SIINVD,1,6) >= CASE WHEN ('$P!{FECHA_ANO_MES}' = '' OR '$P!{FECHA_ANO_MES}' = 'null')
                                           THEN 0
                                           ELSE CASE WHEN LENGTH('$P!{FECHA_ANO_MES}') = 10
                                                     THEN REPLACE(SUBSTR('$P!{FECHA_ANO_MES}', 1, 7), '-', '')
                                                     ELSE '$P!{FECHA_ANO_MES}' END END
      AND SIH.IHDOCN LIKE CASE WHEN ('$P!{FAC}' = '' OR '$P!{FAC}' = 'null')
                               THEN ('%')
                               ELSE '$P!{FAC}%' END
      AND SIH.IHDPFX = 'EX'
      AND  IHDTYP = '1'
      GROUP BY SIH.IHDOCN, RCM.CNME, SIH.SIINVD
ORDER BY SIH.IHDOCN

Although this works, in some cases it does not generate the same values as the previous query, this is due to the distinct that was applied at the beginning, is there any way to apply those distinct to the new query so that it works correctly?

CodePudding user response：

WITH FACTURA AS (SELECT DISTINCT X, Y, Z FROM TABLE)
SELECT FAC.*
FROM FACTURA FAC

is exactly the same as:

SELECT FAC.*
FROM (SELECT DISTINCT X, Y, Z FROM TABLE) FAC

So instead of trying to merge all the joins from both queries into one, you just make the CTE an inline subquery and keep it's DISTINCT behavior