Hi,
I help people in oracle sql, PLSQL but at this time I needed help :), quick help will be much appreciated,
e.g.
I have table lets say GET_BAYID_TMP2, in that table below rows are there, my project requirement is to get the shortest length of two poles
including all the rows of that shortest length to integrate with Java UI,
GET_BAYID_TMP2 Data STRUCTURE1(from_pole) STRUCTURE1(to_pole) LENGTHH BAYID
P001 P002 52 B3
P002 P005 50 B4
P005 P006 48 B6
P006 P007 49 B7
P007 P009 51 B8
P015 P020 48 B1
P001 P015 50 B2
P004 P009 55 B10
P002 P004 50 B9
CREATE TABLE GET_BAYID_TMP2
( STRUCTURE1 VARCHAR2(30 BYTE),
STRUCTURE2 VARCHAR2(30 BYTE),
LENGTHH NUMBER(4,0),
BAYID VARCHAR2(30 BYTE)
) ;
REM INSERTING into GET_BAYID_TMP2
SET DEFINE OFF;
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P001','P002',52,'B3');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P002','P005',50,'B4');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P005','P006',48,'B6');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P006','P007',49,'B7');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P007','P009',51,'B8');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P015','P020',48,'B1');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P001','P015',50,'B2');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P004','P009',55,'B10');
Insert into GET_BAYID_TMP2 (STRUCTURE1,STRUCTURE2,LENGTHH,BAYID) values ('P002','P004',50,'B9');So far i a have managed to write SQL to get all the corresponding rows but unable to query on shortest paths data to fetch all rows.SELECT rownum,level cnt,connect_by_isleaf,level+connect_by_isleaf leaflvl,
STRUCTURE1,STRUCTURE2, LENGTHH,BAYID,LPAD(' ', 2*level-1)||sys_connect_by_path(STRUCTURE2,'/')path
FROM GET_BAYID_TMP2
CONNECT BY nocycle PRIOR STRUCTURE2=STRUCTURE1
START WITH STRUCTURE1 = 'P001'
and STRUCTURE2<='P009';
Present OUTPUT which is wrong ROWNUM CNT CONNECT_BY_ISLEAF LEAFLVL STRUCTURE1 STRUCTURE2 LENGTHH BAYID PATH
1 1 0 1 P001 P002 52 B3 /P002
2 2 0 2 P002 P004 50 B9 /P002/P004
3 3 1 4 P004 P009 55 B10 /P002/P004/P009
4 2 0 2 P002 P005 50 B4 /P002/P005
5 3 0 3 P005 P006 48 B6 /P002/P005/P006
6 4 0 4 P006 P007 49 B7 /P002/P005/P006/P007
7 5 1 6 P007 P009 51 B8 /P002/P005/P006/P007/P009
REQUIRED OUTPUT which is CORRECT, REQUIRED HELP in finding the logic
ROWNUM CNT CONNECT_BY_ISLEAF LEAFLVL STRUCTURE1 STRUCTURE2 LENGTHH BAYID PATH
1 1 0 1 P001 P002 52 B3 /P002
2 2 0 2 P002 P004 50 B9 /P002/P004
3 3 1 4 P004 P009 55 B10 /P002/P004/P009
Here's one way to do it:
- Walk traverse the tree once, finding all the paths.
- Get the shortest complete tree by selecting the min(level) partitioned by whether the row is a leaf or not
- Only return the rows that are leaves where the min level you calculated above = this level
This gives you the shortest complete paths:
with tree as (
SELECT connect_by_isleaf isleaf, STRUCTURE1,STRUCTURE2, bayid, lengthh,
LPAD(' ', 2*level-1)||sys_connect_by_path(STRUCTURE2,'/') path,
min(level) over (partition by connect_by_isleaf) shortest,
level lvl
FROM GET_BAYID_TMP2 t
CONNECT BY nocycle PRIOR STRUCTURE2=STRUCTURE1
START WITH STRUCTURE1 = 'P001'
and STRUCTURE2<='P009'
), shortest as (
select path p from tree
where isleaf = 1
and lvl = shortest
)
select * from shortest;
P
/P002/P004/P009 To get the full hierarchy, generate it again. But this time only return rows where structure2 is in this path you've just found:
with tree as (
SELECT connect_by_isleaf isleaf, STRUCTURE1,STRUCTURE2, bayid, lengthh,
LPAD(' ', 2*level-1)||sys_connect_by_path(STRUCTURE2,'/') path,
min(level) over (partition by connect_by_isleaf) shortest,
level lvl
FROM GET_BAYID_TMP2 t
CONNECT BY nocycle PRIOR STRUCTURE2=STRUCTURE1
START WITH STRUCTURE1 = 'P001'
and STRUCTURE2<='P009'
), shortest as (
select path p from tree
where isleaf = 1
and lvl = shortest
)
select
STRUCTURE1,STRUCTURE2, lengthh, bayid,
LPAD(' ', 2*level-1)||sys_connect_by_path(STRUCTURE2,'/') path
from GET_BAYID_TMP2 t cross join shortest s
CONNECT BY nocycle PRIOR STRUCTURE2= STRUCTURE1
and instr(p, structure2) > 0
START WITH STRUCTURE1 = 'P001'
and STRUCTURE2<='P009';
STRUCTURE1 STRUCTURE2 LENGTHH BAYID PATH
P001 P002 52 B3 /P002
P002 P004 50 B9 /P002/P004
P004 P009 55 B10 /P002/P004/P009