devicz devicz - 25 days ago 10
SQL Question

How to inner join with 3 request on the same table

I'm trying to join 3 results of 3 differents requests on the same table :

My table :

TB_DW_CA_DETAIL_AGG
DATE_VOL
NUMERO_VOL
CODE_CLIENT
SITE_PROD
CODE_CLASSE
STATUT_FIAB
CENTRE_PROFIT
TYPE_CA
MONTANT_QUOD
MONTANT_RISTOURNE
MONTANT_AN
MONTANT_TOT_QUOD
MONTANT_TOT_MENS
MONTANT_TOT_AN
DATE_CHGT


My request :

SELECT * FROM (

SELECT
TB_DW_CA_DETAIL_AGG.DATE_VOL AS DATE_VOL,
TB_DW_CA_DETAIL_AGG.NUMERO_VOL,
TB_DW_CA_DETAIL_AGG.CODE_CLIENT,
TB_DW_CA_DETAIL_AGG.SITE_PROD,
TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT,
sum(TB_DW_CA_DETAIL_AGG.MONTANT_QUOD),
TB_DW_CA_DETAIL_AGG.STATUT_FIAB,
sum(TB_DW_CA_DETAIL_AGG.MONTANT_RISTOURNE)
FROM
TB_DW_CA_DETAIL_AGG
WHERE
(
TB_DW_CA_DETAIL_AGG.DATE_VOL <= trunc(sysdate)-2
AND
TO_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'MM')) = CASE WHEN TO_NUMBER(TO_CHAR(sysdate,'DD')) = '1' OR TO_NUMBER(TO_CHAR(sysdate,'DD')) = '2' THEN TO_NUMBER(TO_CHAR(sysdate,'MM'))-1
ELSE TO_NUMBER(TO_CHAR(sysdate,'MM'))
END
AND
To_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'SYYYY')) = CASE WHEN TO_NUMBER(TO_CHAR(sysdate,'MM')) = '1' THEN TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))-1
ELSE TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))
END
AND
TB_DW_CA_DETAIL_AGG.SITE_PROD <> 'LAT'
)
GROUP BY
TB_DW_CA_DETAIL_AGG.DATE_VOL, TB_DW_CA_DETAIL_AGG.NUMERO_VOL, TB_DW_CA_DETAIL_AGG.CODE_CLIENT, TB_DW_CA_DETAIL_AGG.SITE_PROD, TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT, TB_DW_CA_DETAIL_AGG.STATUT_FIAB
ORDER BY TB_DW_CA_DETAIL_AGG.DATE_VOL ASC
) CTE1
INNER JOIN (

select * FROM
(

SELECT
-- TO_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'MM')),
TB_DW_CA_DETAIL_AGG.DATE_VOL AS DATE_VOL,
TB_DW_CA_DETAIL_AGG.NUMERO_VOL,
TB_DW_CA_DETAIL_AGG.CODE_CLIENT,
TB_DW_CA_DETAIL_AGG.SITE_PROD,
TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT,
sum(TB_DW_CA_DETAIL_AGG.MONTANT_QUOD),
TB_DW_CA_DETAIL_AGG.STATUT_FIAB
FROM
TB_DW_CA_DETAIL_AGG
WHERE
( TB_DW_CA_DETAIL_AGG.STATUT_FIAB=2 )
AND
(
TB_DW_CA_DETAIL_AGG.DATE_VOL <= trunc(sysdate)-2
AND
TO_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'MM')) = CASE WHEN TO_NUMBER(TO_CHAR(sysdate,'DD')) = '1' OR TO_NUMBER(TO_CHAR(sysdate,'DD')) = '2' THEN TO_NUMBER(TO_CHAR(sysdate,'MM'))-1
ELSE TO_NUMBER(TO_CHAR(sysdate,'MM'))
END
AND
To_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'SYYYY')) = CASE WHEN TO_NUMBER(TO_CHAR(sysdate,'MM')) = '1' THEN TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))-1
ELSE TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))
END
AND
TB_DW_CA_DETAIL_AGG.SITE_PROD <> 'LAT'
)
GROUP BY
-- TO_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'MM')),
TB_DW_CA_DETAIL_AGG.DATE_VOL,
TB_DW_CA_DETAIL_AGG.NUMERO_VOL,
TB_DW_CA_DETAIL_AGG.CODE_CLIENT,
TB_DW_CA_DETAIL_AGG.SITE_PROD,
TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT,
TB_DW_CA_DETAIL_AGG.STATUT_FIAB
)CTE2
INNER JOIN(
SELECT
TB_DW_CA_DETAIL_AGG.DATE_VOL AS DATE_VOL,
TB_DW_CA_DETAIL_AGG.NUMERO_VOL,
TB_DW_CA_DETAIL_AGG.CODE_CLIENT,
TB_DW_CA_DETAIL_AGG.SITE_PROD,
TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT,
sum(TB_DW_CA_DETAIL_AGG.MONTANT_QUOD),
TB_DW_CA_DETAIL_AGG.STATUT_FIAB
FROM
TB_DW_CA_DETAIL_AGG
WHERE
( TB_DW_CA_DETAIL_AGG.STATUT_FIAB=1 )
AND
(
TB_DW_CA_DETAIL_AGG.DATE_VOL <= trunc(sysdate)-2
AND
TO_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'MM')) = CASE WHEN TO_NUMBER(TO_CHAR(sysdate,'DD')) = '1' OR TO_NUMBER(TO_CHAR(sysdate,'DD')) = '2' THEN TO_NUMBER(TO_CHAR(sysdate,'MM'))-1
ELSE TO_NUMBER(TO_CHAR(sysdate,'MM'))
END
AND
To_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'SYYYY')) = CASE WHEN TO_NUMBER(TO_CHAR(sysdate,'MM')) = '1' THEN TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))-1
ELSE TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))
END
AND
TB_DW_CA_DETAIL_AGG.SITE_PROD <> 'LAT'


)
GROUP BY
TB_DW_CA_DETAIL_AGG.DATE_VOL, TB_DW_CA_DETAIL_AGG.NUMERO_VOL, TB_DW_CA_DETAIL_AGG.CODE_CLIENT, TB_DW_CA_DETAIL_AGG.SITE_PROD, TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT, TB_DW_CA_DETAIL_AGG.STATUT_FIAB
ORDER BY
TB_DW_CA_DETAIL_AGG.DATE_VOL) CTE3
ON CTE2.DATE_VOL = CTE3.DATE_VOL ) CTE4
ON CTE1.DATE_VOL=CTE4.DATE_VOL;


The problem is : I don't know how to join my two groups CTE1 & CTE4. ( at the end of my request )

ON CTE2.DATE_VOL = CTE3.DATE_VOL ) CTE4
ON CTE1.DATE_VOL=CTE4.DATE_VOL;


More informations :

The only difference between these 3 requests is :

( TB_DW_CA_DETAIL_AGG.STATUT_FIAB=2 )
in the WHERE condition, who gives me a different result for :
sum(TB_DW_CA_DETAIL_AGG.MONTANT_QUOD)


I want to join for the same :

DATE_VOL
NUMERO_VOL
CODE_CLIENT
SITE_PROD
CODE_CLASSE
STATUT_FIAB
CENTRE_PROFIT


the result of
sum(TB_DW_CA_DETAIL_AGG.MONTANT_QUOD)
with

( TB_DW_CA_DETAIL_AGG.STATUT_FIAB=2 )
AND
(TB_DW_CA_DETAIL_AGG.STATUT_FIAB=1 )
AND
without this condition.


  • CTE1 is my biggest request ( got all flights )

  • CTE4 = CTE2 & CTE3

  • CTE1 = NO CONDITION ON STATUT_FIAB

  • CTE2 = ( TB_DW_CA_DETAIL_AGG.STATUT_FIAB=2 )

  • CTE3 = ( TB_DW_CA_DETAIL_AGG.STATUT_FIAB=1 )



Thank you a lot for any information.

Answer

Just an idea, do you really need joins here? Or could you just compute the sums with subqueries like this:

SELECT
    TB_DW_CA_DETAIL_AGG.DATE_VOL AS DATE_VOL,
    TB_DW_CA_DETAIL_AGG.NUMERO_VOL,
    TB_DW_CA_DETAIL_AGG.CODE_CLIENT,
    TB_DW_CA_DETAIL_AGG.SITE_PROD,
    TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
    TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT,
    sum(TB_DW_CA_DETAIL_AGG.MONTANT_QUOD) AS sum_all,
    sum(case when TB_DW_CA_DETAIL_AGG.STATUT_FIAB=1 then TB_DW_CA_DETAIL_AGG.MONTANT_QUOD else 0 end) AS sum_fiab_1,
    sum(case when TB_DW_CA_DETAIL_AGG.STATUT_FIAB=2 then TB_DW_CA_DETAIL_AGG.MONTANT_QUOD else 0 end) AS sum_fiab_2,
    TB_DW_CA_DETAIL_AGG.STATUT_FIAB,
    sum(TB_DW_CA_DETAIL_AGG.MONTANT_RISTOURNE)
FROM
    TB_DW_CA_DETAIL_AGG
WHERE (
    TB_DW_CA_DETAIL_AGG.DATE_VOL <= trunc(sysdate)-2
    AND TO_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'MM')) = CASE
        WHEN TO_NUMBER(TO_CHAR(sysdate,'DD')) = '1' OR TO_NUMBER(TO_CHAR(sysdate,'DD')) = '2'
        THEN TO_NUMBER(TO_CHAR(sysdate,'MM'))-1
        ELSE TO_NUMBER(TO_CHAR(sysdate,'MM'))
    END
    AND To_NUMBER(TO_CHAR(TB_DW_CA_DETAIL_AGG.DATE_VOL,'SYYYY')) = CASE
        WHEN TO_NUMBER(TO_CHAR(sysdate,'MM')) = '1'
        THEN TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))-1
        ELSE TO_NUMBER(TO_CHAR(sysdate,'SYYYY'))
    END
    AND TB_DW_CA_DETAIL_AGG.SITE_PROD  <>  'LAT'
)
GROUP BY
    TB_DW_CA_DETAIL_AGG.DATE_VOL,
    TB_DW_CA_DETAIL_AGG.NUMERO_VOL,
    TB_DW_CA_DETAIL_AGG.CODE_CLIENT,
    TB_DW_CA_DETAIL_AGG.SITE_PROD,
    TB_DW_CA_DETAIL_AGG.CODE_CLASSE,
    TB_DW_CA_DETAIL_AGG.CENTRE_PROFIT,
    TB_DW_CA_DETAIL_AGG.STATUT_FIAB
ORDER BY
     TB_DW_CA_DETAIL_AGG.DATE_VOL ASC