Shortest length recursive path to fetch all rows ,oracle SQL PLSQL

Thirupathi Lagishetti
Thirupathi Lagishetti used Ask the Experts™
on
Data

"BAYID" "STRUCTURE1"    "STRUCTURE2"    "NETWORKTYPE"   "LENGTHH"
"B1"    "P020"  "P015"  "HV"    48
"B2"    "P015"  "P001"  "HV"    50
**"B10" "P009"  "P004"  "HV"    55**
**"B9"  "P004"  "P002"  "HV"    50**
**"B3"  "P001"  "P002"  "HV"    52**
"B4"    "P002"  "P005"  "HV"    50
"B6"    "P005"  "P006"  "HV"    48
"B7"    "P006"  "P007"  "HV"    49
"B8"    "P007"  "P009"  "HV"    51

Input

Pole ID from    - P001
Pole ID To -    P009


Present output

"LEVL"  "STRUCTURES_PATH"            "BAYIDS_PATH"  "SHORTEST_DIST_IN_MTRS"
3        "P001->P002->P004->P009"   "B3->B9->B10"   157


Required output

BAY_ID  BAY_LENGTH  POLE_ID_FROM    POLE_ID_TO
B10     55          P004            P009
B9      50          P002            P004
B3      52          P001            P002


Present code

--Run this SQL for finding shortest path  
WITH BAYS_BOTH_WAY AS
  ( SELECT STRUCTURE1, STRUCTURE2, LENGTHH ,BAYID FROM GET_BAYID_TMP
  WHERE NETWORKTYPE='HV' -- Change the network type here
  UNION ALL
  SELECT STRUCTURE2, STRUCTURE1, LENGTHH,BAYID FROM GET_BAYID_TMP
  WHERE NETWORKTYPE='HV' -- Change the network type here
  ),
  PATHS (NODE, STRUCTURES_PATH,BAYID, SHORTEST_DISTANCE, RNK, LEVL) AS
  (SELECT A.STRUCTURE2,
A.STRUCTURE1 || '->' || A.STRUCTURE2,
    A.BAYID,
    A.LENGTHH,
    1,
    1
  FROM BAYS_BOTH_WAY A
  WHERE A.STRUCTURE1= 'P001' --Give the structures starting point here
  UNION ALL
  SELECT A.STRUCTURE2,
P.STRUCTURES_PATH || '->' || A.STRUCTURE2,
P.BAYID || '->' ||A.BAYID BAYID,
P.SHORTEST_DISTANCE + A.LENGTHH, RANK () OVER (PARTITION BY A.STRUCTURE2 ORDER BY P.SHORTEST_DISTANCE + A.LENGTHH),
    P.LEVL                                                     + 1
  FROM PATHS P
  JOIN BAYS_BOTH_WAY A
  ON A.STRUCTURE1 = P.NODE
  AND P.RNK       = 1
  ) CYCLE NODE
  SET IS_CYCLE TO '1' DEFAULT '0' ,
  PATHS_RANKED AS
  (SELECT LEVL,
    NODE,
    STRUCTURES_PATH,
    BAYID,
    SHORTEST_DISTANCE,
    RANK () OVER (PARTITION BY NODE ORDER BY SHORTEST_DISTANCE) RNK_TOT
  FROM PATHS
  WHERE RNK = 1
  AND NODE  = 'P009' --Give the structures end point here
  )
SELECT
--NODE,
  LEVL,
  STRUCTURES_PATH,
  BAYID BAYIDS_PATH,
  SHORTEST_DISTANCE SHORTEST_DIST_IN_MTRS
FROM PATHS_RANKED
WHERE RNK_TOT = 1 --Uncomment for returning shortest path
;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This looks like a homework assignment and as such, our responses are limited.

Do you have any specific questions about the code or assignment?


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial