Oracle database filter with full outer join and coalesce intersect

coalescejoin;oracle

in a Oracle Database if I "shoot"

SELECT 
    RAP.IDPL,
    C41.TIP_COD_DESEU AS CD41,C41.STOC_VAL AS CANT41,
    C42.TIP_COD_DESEU AS CD42,C42.STOC_VAL AS CANT42,
    C43.TIP_COD_DESEU AS CD43,C43.STOC_VAL AS CANT43
FROM CHEST_UL CHT
    INNER JOIN UL_PUNCT_LUCRU rap
        ON cht.fk_id_raportor = rap.id
    FULL OUTER JOIN CH_41_TAB1 C41
        ON C41.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_42_TAB1 C42
        ON C42.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_43_TAB1 C43
        ON C43.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_44_TAB1 C44
        ON C44.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_5_TAB1 C5
        ON C5.FK_ID_CHEST= CHT.ID
WHERE COALESCE (C41.STOC_VAL,C42.STOC_VAL,
                  C43.STOC_VAL,C44.STOC_VAL,
                  C5.CANT_PREL)
        IS NOT NULL

the result is:

IDPL    CD1         CANT41  CD2         CANT42  CD3         CANT43
275522                      10.01.01    28.54       
275522                                          20.01.01    155
275522  10.01.01    102.766             
272343                      10.01.01    123     
272343                                          10.01.01    353
272343  10.01.01    102.766

what can I do to obtain a result like this:

IDPL    COD         CANT41      CANT42      CANT43
275522  10.01.01    102.766     28.54   
275522  20.01.01                            155
272343  10.01.01    102.766     123         353

Best Answer

You can simply group by IPDL:

SELECT 
    RAP.IDPL,
    MAX(C41.TIP_COD_DESEU) AS CD41, MAX(C41.STOC_VAL) AS CANT41,
    MAX(C42.TIP_COD_DESEU) AS CD42, MAX(C42.STOC_VAL) AS CANT42,
    MAX(C43.TIP_COD_DESEU) AS CD43, MAX(C43.STOC_VAL) AS CANT43
FROM CHEST_UL CHT
    INNER JOIN UL_PUNCT_LUCRU rap
        ON cht.fk_id_raportor = rap.id
    FULL OUTER JOIN CH_41_TAB1 C41
        ON C41.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_42_TAB1 C42
        ON C42.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_43_TAB1 C43
        ON C43.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_44_TAB1 C44
        ON C44.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_5_TAB1 C5
        ON C5.FK_ID_CHEST= CHT.ID
WHERE COALESCE (C41.STOC_VAL,C42.STOC_VAL,
                  C43.STOC_VAL,C44.STOC_VAL,
                  C5.CANT_PREL)
        IS NOT NULL
GROUP BY RAP.IDPL

Are you sure you need the FULL joins? Try changing all to LEFT, might return the same result.

If this can be simplified further depends on the actual PK/FK and data...

Related Question