1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
MERGE INTO invoice inD
USING (
SELECT i.product_code, i.invoice_code, i.customer_code, i.deliveried_quantity,
max(f.prs_ssa) KEEP(DENSE_RANK LAST ORDER BY i.invoice_date) prs_ssa,
max(f.csc) KEEP(DENSE_RANK LAST ORDER BY i.invoice_date) csc,
max(f.prg) KEEP(DENSE_RANK LAST ORDER BY i.invoice_date) prg,
FROM invoice i
JOIN factures f
ON (i.customer_code = f.code_client
AND i.invoice_code = f.numero_facture
AND i.product_code = f.code_article
AND i.deliveried_quantity = f.quantite
AND i.invoice_date > f.date_facture)
GROUP BY i.product_code, i.invoice_code, i.deliveried_quantity, i.customer_code
) s
ON (i.product_code = s.product_code,
i.invoice_code = s.invoice_code,
i.customer_code = s.customer_code,
i.deliveried_quantity = s.deliveried_quantity)
WHERE inD.csc_euro IS NULL
OR inD.prg_euro IS NULL
OR inD.prs_ssa_euro IS NULL
WHEN MATCHED THEN UPDATE inD.prs_ssa_euro = f.prs_ssa, inD.csc_euro = f.csc, inD.prg_euro = f.prg; |
Partager