Home » SQL & PL/SQL » SQL & PL/SQL » How to keep only one branch after connect by (Oracle 11g - 11.2.0.3)
| How to keep only one branch after connect by [message #644013] |
Mon, 26 October 2015 03:36  |
 |
ric90
Messages: 42
Registered: May 2011
Location: Belfort
|
Member |
|
|
Hi all,
I'm looking for a way to keep only one branch after a connect by prior.
I need to find the min date of this branch.
with t as
(
select 'A' child, 'B' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'B' child, 'C' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'C' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/03/2015', 'DD/MM/YYYY') end_date from dual union all
select 'C' child, 'E' parent, to_date('01/04/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'E' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'D' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'G' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'H' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual
)
select child, lpad(' ', 2*level, ' ')|| parent, SYS_CONNECT_BY_PATH(parent, '|') PATH, level lvl,
end_date, prior end_date
from t
start with child = 'A'
connect by NOCYCLE child = prior parent
;
A B |B 1 31/12/2015 00:00:00
B C |B|C 2 31/12/2015 00:00:00 31/12/2015 00:00:00
C D |B|C|D 3 31/03/2015 00:00:00 31/12/2015 00:00:00
D F |B|C|D|F 4 31/12/2015 00:00:00 31/03/2015 00:00:00
C E |B|C|E 3 31/12/2015 00:00:00 31/12/2015 00:00:00
E F |B|C|E|F 4 31/12/2015 00:00:00 31/12/2015 00:00:00
I know, before lauching the sql statement which leaf i'm looking for.
In the sample, PATH must be equal to |B|C|D|F where IS_CONNECT_BY_LEAF = 1.
I'm looking for a solution but can't find any....
Thank you in advance for you help.
Rich
|
|
|
|
| Re: How to keep only one branch after connect by [message #644021 is a reply to message #644013] |
Mon, 26 October 2015 04:24  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Just:
SQL> with t as
2 (
3 select 'A' child, 'B' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
4 select 'B' child, 'C' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
5 select 'C' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/03/2015', 'DD/MM/YYYY') end_date from dual union all
6 select 'C' child, 'E' parent, to_date('01/04/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
7 select 'E' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
8 select 'D' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
9 select 'G' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
10 select 'H' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual
11 ),
12 paths as (
13 select child, lpad(' ', 2*level, ' ')|| parent parent, SYS_CONNECT_BY_PATH(parent, '|') PATH, level lvl,
14 end_date, prior end_date
15 from t
16 where connect_by_isleaf = 1
17 start with child = 'A'
18 connect by NOCYCLE child = prior parent
19 )
20 select * from paths where path = '|B|C|D|F'
21 /
C PARENT PATH LVL END_DATE PRIOREND_DATE
- ---------- ---------- ---------- ------------------- -------------------
D F |B|C|D|F 4 31/12/2015 00:00:00 31/03/2015 00:00:00
1 row selected.
[Updated on: Mon, 26 October 2015 04:25] Report message to a moderator |
|
|
|
| Re: How to keep only one branch after connect by [message #644034 is a reply to message #644021] |
Mon, 26 October 2015 07:58 |
|
ric90
Messages: 42
Registered: May 2011
Location: Belfort
|
Member |
|
|
Hello Michel,
Thank you for your quick answer.
But what i need is the min date for the bracnh that lead me to this path |B|C|D|F .
I think that the exemple is too easy  i tried with PRIOR end date.
But when i have a huge branch and having the min at the middle of the branh, i should do a recursively prior to have the right start date.
with t as
(
select 'A' child, 'B' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'B' child, 'C' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'C' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/03/2015', 'DD/MM/YYYY') end_date from dual union all
select 'C' child, 'E' parent, to_date('01/04/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'E' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'D' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'G' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'E' child, 'F' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'F' child, 'Z' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'Z' child, 'Y' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'Z' child, 'X' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'Z' child, 'W' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'Z' child, 'V' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'V' child, 'S' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'V' child, 'R' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'R' child, 'Q' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual union all
select 'H' child, 'D' parent, to_date('01/01/2015', 'DD/MM/YYYY') start_date, to_date('31/12/2015', 'DD/MM/YYYY') end_date from dual
)
select child, lpad(' ', 2*level, ' ')|| parent, SYS_CONNECT_BY_PATH(parent, '|') PATH, level lvl,
end_date, prior end_date
from t
start with child = 'A'
connect by NOCYCLE child = prior parent --and start_date between prior start_date and prior end_date
;
A B |B 1 31/12/2015 00:00:00
B C |B|C 2 31/12/2015 00:00:00 31/12/2015 00:00:00
C D |B|C|D 3 31/03/2015 00:00:00 31/12/2015 00:00:00
D F |B|C|D|F 4 31/12/2015 00:00:00 31/03/2015 00:00:00
F Z |B|C|D|F|Z 5 31/12/2015 00:00:00 31/12/2015 00:00:00
Z V |B|C|D|F|Z|V 6 31/12/2015 00:00:00 31/12/2015 00:00:00
V R |B|C|D|F|Z|V|R 7 31/12/2015 00:00:00 31/12/2015 00:00:00
R Q |B|C|D|F|Z|V|R|Q 8 31/12/2015 00:00:00 31/12/2015 00:00:00
V S |B|C|D|F|Z|V|S 7 31/12/2015 00:00:00 31/12/2015 00:00:00
Z W |B|C|D|F|Z|W 6 31/12/2015 00:00:00 31/12/2015 00:00:00
Z X |B|C|D|F|Z|X 6 31/12/2015 00:00:00 31/12/2015 00:00:00
Z Y |B|C|D|F|Z|Y 6 31/12/2015 00:00:00 31/12/2015 00:00:00
C E |B|C|E 3 31/12/2015 00:00:00 31/12/2015 00:00:00
E F |B|C|E|F 4 31/12/2015 00:00:00 31/12/2015 00:00:00
F Z |B|C|E|F|Z 5 31/12/2015 00:00:00 31/12/2015 00:00:00
Z V |B|C|E|F|Z|V 6 31/12/2015 00:00:00 31/12/2015 00:00:00
V R |B|C|E|F|Z|V|R 7 31/12/2015 00:00:00 31/12/2015 00:00:00
R Q |B|C|E|F|Z|V|R|Q 8 31/12/2015 00:00:00 31/12/2015 00:00:00
V S |B|C|E|F|Z|V|S 7 31/12/2015 00:00:00 31/12/2015 00:00:00
Z W |B|C|E|F|Z|W 6 31/12/2015 00:00:00 31/12/2015 00:00:00
Z X |B|C|E|F|Z|X 6 31/12/2015 00:00:00 31/12/2015 00:00:00
Z Y |B|C|E|F|Z|Y 6 31/12/2015 00:00:00 31/12/2015 00:00:00
E F |B|C|E|F 4 31/12/2015 00:00:00 31/12/2015 00:00:00
F Z |B|C|E|F|Z 5 31/12/2015 00:00:00 31/12/2015 00:00:00
Z V |B|C|E|F|Z|V 6 31/12/2015 00:00:00 31/12/2015 00:00:00
V R |B|C|E|F|Z|V|R 7 31/12/2015 00:00:00 31/12/2015 00:00:00
R Q |B|C|E|F|Z|V|R|Q 8 31/12/2015 00:00:00 31/12/2015 00:00:00
V S |B|C|E|F|Z|V|S 7 31/12/2015 00:00:00 31/12/2015 00:00:00
Z W |B|C|E|F|Z|W 6 31/12/2015 00:00:00 31/12/2015 00:00:00
Z X |B|C|E|F|Z|X 6 31/12/2015 00:00:00 31/12/2015 00:00:00
The same one, with more hiererchical level with the final path = |B|C|D|F|Z|V|R|Q
that is the almost the reality of my database.... how can i do to find the same min date....
|
|
|
|
Goto Forum:
Current Time: Mon Jul 13 19:53:22 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
