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
| SELECT lev.cpy_lev_n "lev N",
SUBSTR(path, 2, INSTR(path, ',',1,2) - 2) AS col1,
SUBSTR(path, INSTR(path, ',', 1,2)+1, INSTR(path, ',',1,3) - INSTR(path, ',', 1,2) - 1) AS col2,
SUBSTR(path, INSTR(path, ',', 1,3)+1, INSTR(path, ',',1,4) - INSTR(path, ',', 1,3) - 1) AS col3
LEVEL,
lev.cpy_lev_n1 "lev N1",
cpy.cpy_long_nm "COMPANY_NAME",
roce.exercise,
roce.period_dt,
roce.oi_mat,
roce.roce
FROM (
SELECT lev.cpy_lev_n,
SYS_CONNECT_BY_PATH(LPAD(cpy.cpy_long_nm, ',') ||',' AS path, LEVEL,
lev.cpy_lev_n1,
cpy.cpy_long_nm,
roce.exercise,
roce.period_dt,
roce.oi_mat,
roce.roce
FROM sck_cpy_level lev,
sck_roce roce,
sck_ctrl_cpy cpy
WHERE roce.cpy_lev = lev.cpy_lev_n
AND roce.cpy_lev = cpy.comp_cde START WITH cpy_lev_n = 'N9999' CONNECT BY PRIOR cpy_lev_n = cpy_lev_n1
) |
Partager