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 47 48 49 50 51 52 53 54 55 56
|
WITH tree_data_os
AS (
SELECT os.rown
,os.is_leaf
,os.key
,os.old_parent
,os.new_parent
--,os.is_invalid
--,os.tree_path_all
--,os.tree_path_valid_clean
FROM
(
SELECT os.rown
,os.is_leaf
,os.key
,os.parent as old_parent
--,os.is_invalid
--,os.tree_path_all
--,os.tree_path_valid_clean
,CASE WHEN instr(os.tree_path_valid_clean, '/', -1, 2) > 0 THEN
substr(
substr(os.tree_path_valid_clean,
instr(os.tree_path_valid_clean, '/', -1, 2)),
2,
length('/' || os.key) -1
)
ELSE
NULL
END as new_parent
FROM (SELECT rownum as rown
,CONNECT_BY_ROOT(os.key) as root
,connect_by_isleaf AS is_leaf
,os.key
,os.parent
--,os.is_invalid
,regexp_replace(sys_connect_by_path(os.key_valid,'/'),'/+', '/') as tree_path_valid_clean
--,sys_connect_by_path(os.key,'/') as tree_path_all
,os.key_valid
FROM (SELECT DISTINCT os.key
,os.parent
-- ,CASE WHEN instr(os.key,'INVALID') > 0 THEN 1 ELSE 0 END AS is_invalid
,CASE WHEN instr(os.key ,'INVALID') > 0 THEN NULL ELSE os.key END AS key_valid
FROM originalsequence os) os
CONNECT BY PRIOR os.key = os.parent
START WITH PARENT IS NULL) os
WHERE os.key_valid IS NOT NULL) os)
SELECT tos1.key AS key,
tos2.key AS new_child,
tos1.new_parent AS new_parent,
tos1.rown as order1,
tos2.rown as order2
FROM tree_data_os tos1
LEFT OUTER JOIN tree_data_os tos2
ON tos1.key = tos2.new_parent
ORDER BY tos1.rown, tos2.rown |
Partager