1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| WITH
FLT_LIV AS (
SELECT a.ref_cmd, b.ref_prod, b.qte_liv
FROM livraison a
JOIN lignelivraison b ON ( a.ref_liv = b.ref_liv)
),
FLT_LIV_CUMUL AS (
SELECT ref_cmd, ref_prod, sum(qte_liv) qte_liv
FROM FLT_LIV
GROUP BY ref_cmd, ref_prod
),
FLT_CDE_ETAT AS (
SELECT a.ref_cmd, a.ref_prod, a.qte_cdee, nvl(b.qte_liv,0) qte_liv
FROM lignecommande a
LEFT JOIN FLT_LIV_CUMUL b ON ( a.ref_cmd = b.ref_cmd AND a.ref_prod = b.ref_prod)
)
SELECT *
FROM FLT_CDE_ETAT
WHERE qte_cdee<>qte_liv; |
Partager