Home » SQL & PL/SQL » SQL & PL/SQL » Recursive subquery factoring (11.2.0.30)
| Recursive subquery factoring [message #689272] |
Mon, 06 November 2023 14:24  |
Amine
Messages: 376
Registered: March 2010
|
Senior Member |
|
|
Hi all,
I have this table
drop table t_hierarchy_test;
create table t_hierarchy_test
(
id number ,
id_dpt number ,
id_dpt_sup number ,
id_post number
);
alter table t_hierarchy_test add constraint PK_t_hierarchy_test primary key(id, id_dpt);
insert into t_hierarchy_test values (100,1470,null,4567);
insert into t_hierarchy_test values (100,843,1470,5883);
insert into t_hierarchy_test values (100,1578,1470,5284);
insert into t_hierarchy_test values (100,12422,2285,3472);
insert into t_hierarchy_test values (100,12826,2285,2452);
insert into t_hierarchy_test values (100,12827,2285,2226);
insert into t_hierarchy_test values (100,1735,1470,11459);
insert into t_hierarchy_test values (100,12416,1735,1185);
insert into t_hierarchy_test values (100,12828,1735,28751);
insert into t_hierarchy_test values (100,12423,1735,1688);
insert into t_hierarchy_test values (100,12419,1735,1902);
insert into t_hierarchy_test values (100,18730,12419,null);
insert into t_hierarchy_test values (100,1728,1470,11364);
insert into t_hierarchy_test values (100,12424,1728,2649);
insert into t_hierarchy_test values (100,18731,12424,6646);
insert into t_hierarchy_test values (100,18732,12425,6645);
insert into t_hierarchy_test values (100,12418,1728,2231);
insert into t_hierarchy_test values (100,12417,1728,2186);
insert into t_hierarchy_test values (100,1727,1470,11363);
insert into t_hierarchy_test values (100,12829,1727,2710);
insert into t_hierarchy_test values (100,12830,1727,2711);
insert into t_hierarchy_test values (100,1672,1470,11247);
insert into t_hierarchy_test values (100,16034,1470,1211);
insert into t_hierarchy_test values (100,11554,1470,null);
insert into t_hierarchy_test values (100,7646,12421,null);
insert into t_hierarchy_test values (100,7647,12423,null);
insert into t_hierarchy_test values (100,7648,12424,7408);
insert into t_hierarchy_test values (100,7649,12425,7408);
insert into t_hierarchy_test values (100,6390,1470,5916);
insert into t_hierarchy_test values (100,20026,1470,1643);
insert into t_hierarchy_test values (100,2285,1470,11322);
insert into t_hierarchy_test values (100,12421,1735,2666);
insert into t_hierarchy_test values (100,12425,1728,2571);
insert into t_hierarchy_test values (100,12834,1672,2406);
insert into t_hierarchy_test values (100,130178,130171,null);
insert into t_hierarchy_test values (100,130183,130171,null);
insert into t_hierarchy_test values (100,130185,130166,25310);
insert into t_hierarchy_test values (100,130171,130166,1687);
insert into t_hierarchy_test values (100,130166,1470,55948);
insert into t_hierarchy_test values (100,17675,1470,null);
insert into t_hierarchy_test values (100,18797,1470,43354);
insert into t_hierarchy_test values (100,17678,1578,null);
insert into t_hierarchy_test values (100,22661,1578,null);
insert into t_hierarchy_test values (100,220822,130166,55859);
insert into t_hierarchy_test values (100,220824,220822,55875);
insert into t_hierarchy_test values (100,220825,220822,25310);
insert into t_hierarchy_test values (100,220838,130166,10355);
insert into t_hierarchy_test values (100,220839,220838,1688);
insert into t_hierarchy_test values (100,220840,220838,12652);
insert into t_hierarchy_test values (100,220841,220840,55897);
insert into t_hierarchy_test values (100,220842,220839,null);
insert into t_hierarchy_test values (100,220843,220839,55908);
insert into t_hierarchy_test values (100,220845,220840,9605);
insert into t_hierarchy_test values (100,220846,130166,45857);
insert into t_hierarchy_test values (100,220847,220846,51313);
insert into t_hierarchy_test values (100,220848,220846,55882);
insert into t_hierarchy_test values (100,220849,220847,55914);
insert into t_hierarchy_test values (100,220850,220847,36742);
insert into t_hierarchy_test values (100,220851,220848,55916);
insert into t_hierarchy_test values (100,220852,220848,7093);
insert into t_hierarchy_test values (100,220853,130166,55871);
insert into t_hierarchy_test values (100,220854,220853,1154);
insert into t_hierarchy_test values (100,220855,220853,27907);
create or replace view v_hierarchy_test as
select *
from
(
with v_hierarchy_rsf(id, id_dpt, id_dpt_sup, id_post, lvl, path_id_dpt, path_id_post)
as
(
select id, id_dpt, id_dpt_sup, id_post, 1 lvl, to_char(id_dpt) path_id_dpt, to_char(id_post) path_id_post
from t_hierarchy_test
where 1 = 1
and id_dpt_sup is null
union all
select t_hierarchy_test.id, t_hierarchy_test.id_dpt, t_hierarchy_test.id_dpt_sup, t_hierarchy_test.id_post, lvl + 1
, t_hierarchy_test.id_dpt || '/' || v_hierarchy_rsf.path_id_dpt path_id_dpt
, t_hierarchy_test.id_post || '/' || v_hierarchy_rsf.path_id_post path_id_post
from t_hierarchy_test, v_hierarchy_rsf
where 1 = 1
and t_hierarchy_test.id_dpt_sup = v_hierarchy_rsf.id_dpt
and t_hierarchy_test.id = v_hierarchy_rsf.id
)
search
depth
--breadth
first by id, id_dpt set order1
cycle id, id_dpt set cycle to 1 default 0
select
id
, id_dpt
, id_dpt_sup
, id_post
, lvl
, path_id_dpt
, path_id_post
from v_hierarchy_rsf
order by order1
)
;
For each id_dpt with lvl >= 3, we have to create a path based on the id_post of their parents.
And we have to alternate between + and - to construct the path.
For example :
id_dpt = 220841
As we can wee the path of all its id_post parents is 55897/12652/10355/55948/4567.
In my requirement, i have to delete the root's id_post and the current id_post.
So I'll have this construction : 12652/10355/55948.
Now, I need to alternate between + and - so the last id_post must always be a +.
So I have : 12652/10355+55948
then : 12652-10355+55948
then : +12652-10355+55948
The desired output for each id_dpt is shown in the column path_id_post_d
clear scr
column path_id_post format a30
column path_id_post_d format a30
column path_id_dpt format a30
set linesize 200
select id, id_dpt, id_post, lvl, path_id_post, path_id_post path_id_post_d
from v_hierarchy_test
where lvl >= 3
order by lvl
;
ID ID_DPT ID_POST LVL PATH_COD_POST PATH_COD_POST_D
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
100 17678 3 /5284/4567 +5284
100 22661 3 /5284/4567 +5284
100 220853 55871 3 55871/55948/4567 +55948
100 220846 45857 3 45857/55948/4567 +55948
100 220838 10355 3 10355/55948/4567 +55948
100 220822 55859 3 55859/55948/4567 +55948
100 130185 25310 3 25310/55948/4567 +55948
100 130171 1687 3 1687/55948/4567 +55948
100 12827 2226 3 2226/11322/4567 +11322
100 12826 2452 3 2452/11322/4567 +11322
100 12422 3472 3 3472/11322/4567 +11322
ID ID_DPT ID_POST LVL PATH_COD_POST PATH_COD_POST_D
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
100 12828 28751 3 28751/11459/4567 +11459
100 12423 1688 3 1688/11459/4567 +11459
100 12421 2666 3 2666/11459/4567 +11459
100 12419 1902 3 1902/11459/4567 +11459
100 12416 1185 3 1185/11459/4567 +11459
100 12425 2571 3 2571/11364/4567 +11364
100 12424 2649 3 2649/11364/4567 +11364
100 12418 2231 3 2231/11364/4567 +11364
100 12417 2186 3 2186/11364/4567 +11364
100 12830 2711 3 2711/11363/4567 +11363
100 12829 2710 3 2710/11363/4567 +11363
ID ID_DPT ID_POST LVL PATH_COD_POST PATH_COD_POST_D
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
100 12834 2406 3 2406/11247/4567 +11247
100 130183 4 /1687/55948/4567 -1687+55948
100 18731 6646 4 6646/2649/11364/4567 -2649+11364
100 7647 4 /1688/11459/4567 -1688+11459
100 220824 55875 4 55875/55859/55948/4567 -55859+55948
100 220825 25310 4 25310/55859/55948/4567 -55859+55948
100 220855 27907 4 27907/55871/55948/4567 -55871+55948
100 7648 7408 4 7408/2649/11364/4567 -2649+11364
100 130178 4 /1687/55948/4567 -1687+55948
100 7646 4 /2666/11459/4567 -2666+11459
100 220840 12652 4 12652/10355/55948/4567 -10355+55948
ID ID_DPT ID_POST LVL PATH_COD_POST PATH_COD_POST_D
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
100 18730 4 /1902/11459/4567 -1902+11459
100 220847 51313 4 51313/45857/55948/4567 -45857+55948
100 220854 1154 4 1154/55871/55948/4567 -55871+55948
100 18732 6645 4 6645/2571/11364/4567 -2571+11364
100 7649 7408 4 7408/2571/11364/4567 -2571+11364
100 220839 1688 4 1688/10355/55948/4567 -10355+55948
100 220848 55882 4 55882/45857/55948/4567 -45857+55948
100 220851 55916 5 55916/55882/45857/55948/4567 +55882-45857+55948
100 220852 7093 5 7093/55882/45857/55948/4567 +55882-45857+55948
100 220842 5 /1688/10355/55948/4567 +1688-10355+55948
100 220843 55908 5 55908/1688/10355/55948/4567 +1688-10355+55948
ID ID_DPT ID_POST LVL PATH_COD_POST PATH_COD_POST_D
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
100 220841 55897 5 55897/12652/10355/55948/4567 +12652-10355+55948
100 220845 9605 5 9605/12652/10355/55948/4567 +12652-10355+55948
100 220849 55914 5 55914/51313/45857/55948/4567 +51313-45857+55948
100 220850 36742 5 36742/51313/45857/55948/4567 +51313-45857+55948
48 rows selected.
SQL>
Thanks in advance
Amine
|
|
|
|
| Re: Recursive subquery factoring [message #689273 is a reply to message #689272] |
Mon, 06 November 2023 18:02  |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following applies a function to one column of your existing view.
-- function and query:
create or replace function get_path_id_post_d
(p_path_id_post in varchar2)
return varchar2
as
v_path_id_post_d varchar2(4000);
v_count number;
begin
-- delete the root's id_post and the current id_post
-- and replace all / with + :
v_path_id_post_d :=
replace
(substr
(p_path_id_post,
instr (p_path_id_post, '/', 1, 1),
instr (p_path_id_post, '/', -1, 1) - instr (p_path_id_post, '/', 1, 1)),
'/',
'+');
v_count := regexp_count (v_path_id_post_d, '\+') - 1;
-- replace alterating + with - so the last id_post is always + :
while v_count > 0 loop
v_path_id_post_d := regexp_replace (v_path_id_post_d, '\+', '-', 1, v_count);
v_count := v_count - 2;
end loop;
return v_path_id_post_d;
end get_path_id_post_d;
/
show errors
select id, id_dpt, id_post, lvl, path_id_post,
get_path_id_post_d (path_id_post) as path_id_post_d
from v_hierarchy_test
where lvl >= 3
order by lvl
/
-- execution:
SCOTT@orcl_12.1.0.2.0> create or replace function get_path_id_post_d
2 (p_path_id_post in varchar2)
3 return varchar2
4 as
5 v_path_id_post_d varchar2(4000);
6 v_count number;
7 begin
8 -- delete the root's id_post and the current id_post
9 -- and replace all / with + :
10 v_path_id_post_d :=
11 replace
12 (substr
13 (p_path_id_post,
14 instr (p_path_id_post, '/', 1, 1),
15 instr (p_path_id_post, '/', -1, 1) - instr (p_path_id_post, '/', 1, 1)),
16 '/',
17 '+');
18 v_count := regexp_count (v_path_id_post_d, '\+') - 1;
19 -- replace alterating + with - so the last id_post is always + :
20 while v_count > 0 loop
21 v_path_id_post_d := regexp_replace (v_path_id_post_d, '\+', '-', 1, v_count);
22 v_count := v_count - 2;
23 end loop;
24 return v_path_id_post_d;
25 end get_path_id_post_d;
26 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> select id, id_dpt, id_post, lvl, path_id_post,
2 get_path_id_post_d (path_id_post) as path_id_post_d
3 from v_hierarchy_test
4 where lvl >= 3
5 order by lvl
6 /
ID ID_DPT ID_POST LVL PATH_ID_POST PATH_ID_POST_D
---------- ---------- ---------- ---------- ------------------------------ ------------------------------
100 17678 3 /5284/4567 +5284
100 22661 3 /5284/4567 +5284
100 220853 55871 3 55871/55948/4567 +55948
100 220846 45857 3 45857/55948/4567 +55948
100 220838 10355 3 10355/55948/4567 +55948
100 220822 55859 3 55859/55948/4567 +55948
100 130185 25310 3 25310/55948/4567 +55948
100 130171 1687 3 1687/55948/4567 +55948
100 12827 2226 3 2226/11322/4567 +11322
100 12826 2452 3 2452/11322/4567 +11322
100 12422 3472 3 3472/11322/4567 +11322
100 12828 28751 3 28751/11459/4567 +11459
100 12423 1688 3 1688/11459/4567 +11459
100 12421 2666 3 2666/11459/4567 +11459
100 12419 1902 3 1902/11459/4567 +11459
100 12416 1185 3 1185/11459/4567 +11459
100 12425 2571 3 2571/11364/4567 +11364
100 12424 2649 3 2649/11364/4567 +11364
100 12418 2231 3 2231/11364/4567 +11364
100 12417 2186 3 2186/11364/4567 +11364
100 12830 2711 3 2711/11363/4567 +11363
100 12829 2710 3 2710/11363/4567 +11363
100 12834 2406 3 2406/11247/4567 +11247
100 130183 4 /1687/55948/4567 -1687+55948
100 18731 6646 4 6646/2649/11364/4567 -2649+11364
100 7647 4 /1688/11459/4567 -1688+11459
100 220824 55875 4 55875/55859/55948/4567 -55859+55948
100 220825 25310 4 25310/55859/55948/4567 -55859+55948
100 220855 27907 4 27907/55871/55948/4567 -55871+55948
100 7648 7408 4 7408/2649/11364/4567 -2649+11364
100 130178 4 /1687/55948/4567 -1687+55948
100 7646 4 /2666/11459/4567 -2666+11459
100 220840 12652 4 12652/10355/55948/4567 -10355+55948
100 18730 4 /1902/11459/4567 -1902+11459
100 220847 51313 4 51313/45857/55948/4567 -45857+55948
100 220854 1154 4 1154/55871/55948/4567 -55871+55948
100 18732 6645 4 6645/2571/11364/4567 -2571+11364
100 7649 7408 4 7408/2571/11364/4567 -2571+11364
100 220839 1688 4 1688/10355/55948/4567 -10355+55948
100 220848 55882 4 55882/45857/55948/4567 -45857+55948
100 220851 55916 5 55916/55882/45857/55948/4567 +55882-45857+55948
100 220852 7093 5 7093/55882/45857/55948/4567 +55882-45857+55948
100 220842 5 /1688/10355/55948/4567 +1688-10355+55948
100 220843 55908 5 55908/1688/10355/55948/4567 +1688-10355+55948
100 220841 55897 5 55897/12652/10355/55948/4567 +12652-10355+55948
100 220845 9605 5 9605/12652/10355/55948/4567 +12652-10355+55948
100 220849 55914 5 55914/51313/45857/55948/4567 +51313-45857+55948
100 220850 36742 5 36742/51313/45857/55948/4567 +51313-45857+55948
48 rows selected.
|
|
|
|
| Re: Recursive subquery factoring [message #689274 is a reply to message #689273] |
Mon, 06 November 2023 21:12 |
 |
mathguy
Messages: 108
Registered: January 2023
|
Senior Member |
|
|
If I understand correctly, then the whole discussion of hierarchical query based on the table, building the paths etc. is just for background. You already have the paths in the PATH_ID_POST column in the view, and you want to get the strings in the PATH_ID_POST_D column in your output illustration, for rows where LVL >= 3. That task, interpreted this way, has nothing to do with the view's background.
You can achieve this with the query I will show below. But the following question may be of some interest: Is that the real-life problem? Or do you need just PATH_ID_POST_D, and not all the other stuff in the view, starting from the base table? In that case, the answer may be more direct: instead of the query creating the view from the base table, you - or we - can write a different query, also starting strictly from the base table, to get PATH_ID_POST_D directly. For that matter, it may be more efficient to do this using a CONNECT BY query instead of recursive subquery factoring; was there a particular reason to avoid CONNECT BY? Perhaps a desire to be able to adapt the code for db products other than Oracle Database?
So - here is a way to get PATH_ID_POST_D directly from PATH_ID_POST. It can be written more efficiently (I think) if needed - if you find that this solution is too slow for your needs. The first pass (PREP1) removes the first and last tokens (ID_POST) from each path, keeping the forward slash at the beginning of each path but removing the last slash with the last token. The second pass (PREP2) changes the forward slashes with alternating - and +, ending with + for the last token. In the case of an odd number of tokens, this leaves just the first delimiter as a slash (at the left end of the string); this is changed to + in the last pass. (Note that when LVL is even, the leading slash was already changed to a "minus" in the second pass, so the third pass will have no effect.)
with
prep1 (path_id_post, modified_path) as (
select path_id_post,
substr(path_id_post, instr(path_id_post, '/'), instr(path_id_post, '/', -1) - instr(path_id_post, '/'))
from v_hierarchy_test
where lvl >= 3
)
, prep2 (path_id_post, almost_final_path) as (
select path_id_post, regexp_replace(modified_path, '(/(\d*)/(\d*))+$', '-\2+\3')
from prep1
)
select path_id_post, regexp_replace(almost_final_path, '^/', '+') as path_id_post_d
from prep2
; |
|
|
|
| Re: Recursive subquery factoring [message #689275 is a reply to message #689274] |
Tue, 07 November 2023 00:21 |
|
mathguy
Messages: 108
Registered: January 2023
|
Senior Member |
|
|
For illustration - here is a single query against the base table, computing at the same time the PATH_ID_POST as you have it in the view and PATH_ID_POST_D as you requested in your question. If you only need the latter and PATH_ID_POST was just an intermediate step in your attempt at solving the problem, you don't need the intermediate step.
A few more things - I don't understand why in the definition of your view you needed the CYCLE clause - there are no cycles in your data (and if there were, it is not clear what the task would be, or whether using the CYCLE clause would solve the problem in an acceptable way). Also, you have the SEARCH clause, which causes the output of the recursive query to be ordered according to the ORDER1 pseudo-column - the ORDER BY clause at the end is superfluous. You might want to add ORDER1 to the SELECT list of the view - otherwise you can't reference it when you select from the view. Either way though, it is not clear why you even need the SEARCH clause at all; in your question, at the end you order by LVL, not by ORDER1.
Also, is COD supposed to be the same as ID (for example: PATH_ID_POST vs PATH_COD_POST - you jump from one to the other with no explanation).
Anyway - here is the query that gets everything directly from the base table:
with
r (id, id_dpt, id_post, lvl, path_id_post, path_id_post_d) as (
select id, id_dpt, id_post, 1, to_char(id_post), cast(null as varchar2(4000))
from t_hierarchy_test
where id_dpt_sup is null
union all
select h.id, h.id_dpt, h.id_post, r.lvl + 1,
h.id_post || '/' || r.path_id_post,
case when r.lvl >= 2 then case mod(r.lvl, 2) when 0 then '+' else '-' end ||
r.id_post || r.path_id_post_d end
from r join t_hierarchy_test h on r.id_dpt = h.id_dpt_sup
)
select id, id_dpt, id_post, lvl, path_id_post, path_id_post_d
from r
where lvl >= 3
order by lvl -- or whatever is needed
; [Updated on: Tue, 07 November 2023 00:22] Report message to a moderator |
|
|
|
|
|
| Re: Recursive subquery factoring [message #689277 is a reply to message #689275] |
Tue, 07 November 2023 06:26 |
Amine
Messages: 376
Registered: March 2010
|
Senior Member |
|
|
mathguy wrote on Tue, 07 November 2023 07:21
A few more things - I don't understand why in the definition of your view you needed the CYCLE clause - there are no cycles in your data (and if there were, it is not clear what the task would be, or whether using the CYCLE clause would solve the problem in an acceptable way). Also, you have the SEARCH clause, which causes the output of the recursive query to be ordered according to the ORDER1 pseudo-column - the ORDER BY clause at the end is superfluous. You might want to add ORDER1 to the SELECT list of the view - otherwise you can't reference it when you select from the view. Either way though, it is not clear why you even need the SEARCH clause at all; in your question, at the end you order by LVL, not by ORDER1.
Actually, for CYCLE and SEARCH it was only for learning purposes, not to resolve my request.
mathguy wrote on Tue, 07 November 2023 07:21
Also, is COD supposed to be the same as ID (for example: PATH_ID_POST vs PATH_COD_POST - you jump from one to the other with no explanation).
They are actually the same, it was a mistake.
mathguy wrote on Tue, 07 November 2023 07:21
Anyway - here is the query that gets everything directly from the base table:
with
r (id, id_dpt, id_post, lvl, path_id_post, path_id_post_d) as (
select id, id_dpt, id_post, 1, to_char(id_post), cast(null as varchar2(4000))
from t_hierarchy_test
where id_dpt_sup is null
union all
select h.id, h.id_dpt, h.id_post, r.lvl + 1,
h.id_post || '/' || r.path_id_post,
case when r.lvl >= 2 then case mod(r.lvl, 2) when 0 then '+' else '-' end ||
r.id_post || r.path_id_post_d end
from r join t_hierarchy_test h on r.id_dpt = h.id_dpt_sup
)
select id, id_dpt, id_post, lvl, path_id_post, path_id_post_d
from r
where lvl >= 3
order by lvl -- or whatever is needed
;
Your query is awsome ! thank you very much !
|
|
|
|
| Re: Recursive subquery factoring [message #689278 is a reply to message #689274] |
Tue, 07 November 2023 06:32 |
Amine
Messages: 376
Registered: March 2010
|
Senior Member |
|
|
mathguy wrote on Tue, 07 November 2023 04:12If I understand correctly, then the whole discussion of hierarchical query based on the table, building the paths etc. is just for background. You already have the paths in the PATH_ID_POST column in the view, and you want to get the strings in the PATH_ID_POST_D column in your output illustration, for rows where LVL >= 3. That task, interpreted this way, has nothing to do with the view's background.
Nothing to do with the view background, only to simplify my problem and get it understandable by experts.
mathguy wrote on Tue, 07 November 2023 04:12
You can achieve this with the query I will show below. But the following question may be of some interest: Is that the real-life problem? Or do you need just PATH_ID_POST_D, and not all the other stuff in the view, starting from the base table? In that case, the answer may be more direct: instead of the query creating the view from the base table, you - or we - can write a different query, also starting strictly from the base table, to get PATH_ID_POST_D directly.
Yeah it's a bijection to a real-life problem.
mathguy wrote on Tue, 07 November 2023 04:12
For that matter, it may be more efficient to do this using a CONNECT BY query instead of recursive subquery factoring; was there a particular reason to avoid CONNECT BY? Perhaps a desire to be able to adapt the code for db products other than Oracle Database?
Beacause with CONNECT BY, the path starts with the id_post_root. I want it to start with the current id_post.
mathguy wrote on Tue, 07 November 2023 04:12
So - here is a way to get PATH_ID_POST_D directly from PATH_ID_POST. It can be written more efficiently (I think) if needed - if you find that this solution is too slow for your needs. The first pass (PREP1) removes the first and last tokens (ID_POST) from each path, keeping the forward slash at the beginning of each path but removing the last slash with the last token. The second pass (PREP2) changes the forward slashes with alternating - and +, ending with + for the last token. In the case of an odd number of tokens, this leaves just the first delimiter as a slash (at the left end of the string); this is changed to + in the last pass. (Note that when LVL is even, the leading slash was already changed to a "minus" in the second pass, so the third pass will have no effect.)
with
prep1 (path_id_post, modified_path) as (
select path_id_post,
substr(path_id_post, instr(path_id_post, '/'), instr(path_id_post, '/', -1) - instr(path_id_post, '/'))
from v_hierarchy_test
where lvl >= 3
)
, prep2 (path_id_post, almost_final_path) as (
select path_id_post, regexp_replace(modified_path, '(/(\d*)/(\d*))+$', '-\2+\3')
from prep1
)
select path_id_post, regexp_replace(almost_final_path, '^/', '+') as path_id_post_d
from prep2
;
Thanks a lot. I prefer mainly the solution you've posted in your last post.
|
|
|
|
| Re: Recursive subquery factoring [message #689282 is a reply to message #689278] |
Tue, 07 November 2023 14:30 |
|
mathguy
Messages: 108
Registered: January 2023
|
Senior Member |
|
|
Beacause with CONNECT BY, the path starts with the id_post_root. I want it to start with the current id_post.
If you need the paths in opposite direction, start from all nodes and traverse towards the root, instead of the other way around. The query below shows what I mean. I expect that this will run quite a bit faster than the recursive subquery factoring approach.
with
prep1 (id, id_dpt, id_post, lvl, pth) as (
select connect_by_root id, connect_by_root id_dpt, connect_by_root id_post, level,
sys_connect_by_path(case mod(level, 2) when 0 then '+' else '-' end || prior id_post, '/')
from t_hierarchy_test
where level >= 3 and connect_by_isleaf = 1
connect by id_dpt = prior id_dpt_sup
)
, prep2 (id, id_dpt, id_post, lvl, almost_final_pth) as (
select id, id_dpt, id_post, lvl, replace(substr(pth, instr(pth, '/', 1, 3) + 1), '/')
from prep1
)
select id, id_dpt, id_post, lvl,
case mod(lvl, 2) when 0 then almost_final_pth
else translate(almost_final_pth, '+-', '-+') end as path_id_post_d
from prep2
; |
|
|
|
Goto Forum:
Current Time: Mon May 19 18:26:35 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
