1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
WITH LIGNENOMENCLATURE AS (
SELECT 10 as codecompo, 1 AS codenomenc, 600 AS quantite FROM DUAL
UNION ALL
SELECT 21 as codecompo, 10 AS codenomenc, 1 AS quantite FROM DUAL
UNION ALL
SELECT 22 as codecompo, 10 AS codenomenc, 0.1 AS quantite FROM DUAL
UNION ALL
SELECT 211 as codecompo, 21 AS codenomenc, 0.01 AS quantite FROM DUAL
UNION ALL
SELECT 212 as codecompo, 21 AS codenomenc, 0.05 AS quantite FROM DUAL
UNION ALL
SELECT 221 as codecompo, 22 AS codenomenc, 0.5 AS quantite FROM DUAL
)
SELECT a.codecompo
,LEVEL
,a.codenomenc
,a.quantite
,(SELECT exp(sum(ln(abis.quantite))) FROM lignenomenclature abis START WITH abis.codecompo = a.codecompo CONNECT BY PRIOR abis.codenomenc = abis.codecompo) AS Total
FROM LIGNENOMENCLATURE a
where a.codecompo is not null
START WITH a.codenomenc = '1'
CONNECT BY PRIOR a.codecompo = a.codenomenc |
Partager