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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| WITH MaTable AS
(
SELECT 2 AS NoSem, 327 AS Pb, 'bbb' AS Code FROM dual union ALL
SELECT 3 , 645 , 'ccc' FROM dual union ALL
SELECT 5 , 349 , 'eee' FROM dual
)
, Cpt AS
(
SELECT LEVEL AS lvl
FROM dual
CONNECT BY LEVEL <= 6
)
, SR AS
(
SELECT cp.lvl AS NoSem
, mt.NoSem AS NoSemOrigine
, coalesce( mt.Pb
, Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl , 6))
, Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +1, 6))
, Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +2, 6))
, Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +3, 6))
, Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +4, 6))
, Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +5, 6))
) AS Pb
, coalesce( mt.Code
, Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl , 6))
, Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +1, 6))
, Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +2, 6))
, Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +3, 6))
, Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +4, 6))
, Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +5, 6))
) AS Code
FROM MaTable mt
RIGHT JOIN Cpt cp
ON mt.NoSem = cp.lvl
)
SELECT NoSem, Pb, Code
FROM SR
WHERE NoSemOrigine IS null
ORDER BY NoSem asc;
NOSEM PB CODE
----- --- ----
1 349 eee
4 645 ccc
6 349 eee |
Partager