Hi,
After looking through plenty of your previous examples i would like to piggy back from an example used many moons ago and ask for a change to the displayed result.
https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:907278700346567560 ops$tkyte%ORA11GR1> select substr( scbp, instr(scbp,'-',1,1)+1, instr(scbp,'-',1,2)-instr(scbp,'-',1,1)-1) l1,
2 substr( scbp, instr(scbp,'-',1,2)+1, instr(scbp,'-',1,3)-instr(scbp,'-',1,2)-1) l2,
3 substr( scbp, instr(scbp,'-',1,3)+1, instr(scbp,'-',1,4)-instr(scbp,'-',1,3)-1) l3,
4 substr( scbp, instr(scbp,'-',1,4)+1, instr(scbp,'-',1,5)-instr(scbp,'-',1,4)-1) l4,
5 substr( scbp, instr(scbp,'-',1,5)+1, instr(scbp,'-',1,6)-instr(scbp,'-',1,5)-1) l5,
6 substr( scbp, instr(scbp,'-',1,6)+1, instr(scbp,'-',1,7)-instr(scbp,'-',1,6)-1) l6
7 from (
8 select SYS_CONNECT_BY_PATH(CHILD_NODE, '-') || '-' scbp
9 from BRANCHES
10 start with PARENT_NODE = ' '
11 connect by prior CHILD_NODE = PARENT_NODE
12 )
13 /
L1 L2 L3 L4 L5 L6
---------- ---------- ---------- ---------- ---------- ----------
LEVEL1
LEVEL1 LEVEL2
LEVEL1 LEVEL2 LEVEL3.1
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.1
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.1 LEVEL5.1
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.1 LEVEL5.2
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.1 LEVEL5.2 LEVEL6.1
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.1 LEVEL5.2 LEVEL6.2
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.2
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.2 LEVEL5.3
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.2 LEVEL5.3 LEVEL6.3
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.2 LEVEL5.3 LEVEL6.4
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.2 LEVEL5.4
LEVEL1 LEVEL2 LEVEL3.1 LEVEL4.2 LEVEL5.4 LEVEL6.5
LEVEL1 LEVEL2 LEVEL3.2
LEVEL1 LEVEL2 LEVEL3.3
LEVEL1 LEVEL2 LEVEL3.3 LEVEL4.3
LEVEL1 LEVEL2 LEVEL3.3 LEVEL4.3 LEVEL5.5
LEVEL1 LEVEL2 LEVEL3.3 LEVEL4.3 LEVEL5.6
LEVEL1 LEVEL2 LEVEL3.3 LEVEL4.4
LEVEL1 LEVEL2 LEVEL3.3 LEVEL4.4 LEVEL5.7
LEVEL1 LEVEL2 LEVEL3.3 LEVEL4.4 LEVEL5.8
How would I only return the values for level 1 and level 2 to create a shortened Hierarchy Path? entries in each field can be different lengths so character limits are not ideal.
Path result would be great if it could look like >LEVEL1>LEVEL2
Any help is appreciated.
I wasn't really sure what you were after - so here's a demo to get the leading and trailing two items
SQL> @drop t
Y1 Y2
-------------------------------------------------------------------------------------------- -------------------------
TABLE cascade constraints purge
1 row selected.
Table dropped.
SQL>
SQL> create table t ( id int, par_id int );
Table created.
SQL>
SQL> insert into t
2 select rownum, decode(rownum,1,null,rownum-1)
3 from dual
4 connect by level <= 10;
10 rows created.
SQL>
SQL> select lpad(id,level*2) id, par_id, sys_connect_by_path(id,'~') tree
2 from t
3 start with par_id is null
4 connect by prior id = par_id;
ID PAR_ID TREE
------------------------------ ---------- ------------------------------
1 ~1
2 1 ~1~2
3 2 ~1~2~3
4 3 ~1~2~3~4
5 4 ~1~2~3~4~5
6 5 ~1~2~3~4~5~6
7 6 ~1~2~3~4~5~6~7
8 7 ~1~2~3~4~5~6~7~8
9 8 ~1~2~3~4~5~6~7~8~9
10 9 ~1~2~3~4~5~6~7~8~9~10
10 rows selected.
SQL>
SQL> with raw_data as
2 (
3 select lpad(id,level*2) id, par_id, sys_connect_by_path(id,'~') tree
4 from t
5 start with par_id is null
6 connect by prior id = par_id
7 )
8 select raw_data.*,
9 case when instr(tree,'~',1,3) > 0 then
10 substr(tree,1,instr(tree,'~',1,3))
11 else
12 tree
13 end first_two,
14 case when regexp_count(tree,'~') > 1 then
15 substr(tree,instr(tree,'~',1,regexp_count(tree,'~')-1))
16 else
17 tree
18 end last_two
19 from raw_data;
ID PAR_ID TREE FIRST_TWO LAST_TWO
------------------------------ ---------- ------------------------------ -------------------- --------------------
1 ~1 ~1 ~1
2 1 ~1~2 ~1~2 ~1~2
3 2 ~1~2~3 ~1~2~ ~2~3
4 3 ~1~2~3~4 ~1~2~ ~3~4
5 4 ~1~2~3~4~5 ~1~2~ ~4~5
6 5 ~1~2~3~4~5~6 ~1~2~ ~5~6
7 6 ~1~2~3~4~5~6~7 ~1~2~ ~6~7
8 7 ~1~2~3~4~5~6~7~8 ~1~2~ ~7~8
9 8 ~1~2~3~4~5~6~7~8~9 ~1~2~ ~8~9
10 9 ~1~2~3~4~5~6~7~8~9~10 ~1~2~ ~9~10
10 rows selected.
SQL>