1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| WITH ARTICLE AS
(
select 1 as "Ref-article", 'A' as "Code-barre" from dual union all
select 1 , 'B' from dual union all
select 2 , 'C' from dual
)
, PRIX_VENTE AS
(
select 1 as "Ref-article", 'A' as "Code-barre", 10 as "Prix" from dual union all
select 1 , null , 20 from dual union all
select 2 , null , 30 from dual
)
SELECT art."Ref-article", art."Code-barre", coalesce(pv1."Prix", pv2."Prix") as "Prix"
FROM ARTICLE art
INNER JOIN PRIX_VENTE pv2
ON pv2."Ref-article" = art."Ref-article"
LEFT OUTER JOIN PRIX_VENTE pv1
ON pv1."Ref-article" = art."Ref-article"
AND pv1."Code-barre" = art."Code-barre"
WHERE pv2."Code-barre" IS NULL
ORDER BY art."Ref-article" asc, art."Code-barre" asc;
Ref-article Code-barre Prix
1 A 10
1 B 20
2 C 30 |
Partager