Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Lag + hierarchy query

RengudiApr 10 2023
Sample data:
(rn,net_id,parent_net_id,net_addr,NETWORK,IP_ADDRESS,PARENT_NODE,LVL,TREE) AS
SELECT 1 AS rn,2 AS net_id,NULL AS parent_net_id,3232235520 AS net_addr,'192.168.0.0/24' AS NETWORK,'192.168.0.0' AS IP_ADDRESS,'192.168.0.0/24' AS PARENT_NODE,1 AS LVL,'/192.168.0.0' AS TREE FROM dual
UNION ALL
SELECT 2,6,2,3232235776,'192.168.1.0/24','192.168.1.0','192.168.0.0/24',2,'/192.168.0.0/192.168.1.0' FROM dual
UNION ALL
SELECT 3,7,2,3232236032,'192.168.2.0/24','192.168.2.0','192.168.0.0/24',2,'/192.168.0.0/192.168.2.0' FROM dual
UNION ALL
SELECT 4,11,6,3232236288,'192.168.1.128/25','192.168.1.128','192.168.1.0/24',3,'/192.168.0.0/192.168.1.0/192.168.1.128' FROM dual
UNION ALL
SELECT 5,12,6,3232236544,'192.168.1.192/26','192.168.1.192','192.168.1.0/24',3,'/192.168.0.0/192.168.1.0/192.168.1.192' FROM dual
UNION ALL
SELECT 6,8,7,3232236288,'192.168.2.128/25','192.168.2.128','192.168.2.0/24',3,'/192.168.0.0/192.168.2.0/192.168.2.128' FROM dual
UNION ALL
SELECT 7,9,7,3232236544,'192.168.2.192/26','192.168.2.192','192.168.2.0/24',3,'/192.168.0.0/192.168.2.0/192.168.2.192' FROM dual
UNION ALL
SELECT 8,13,11,3232236304,'192.168.1.0/26','192.168.1.0','192.168.1.0/24/192.168.1.128',4,'/192.168.0.0/192.168.1.0/192.168.1.128/192.168.1.0' FROM dual;

Query:-          


		  select * from (SELECT ROWNUM
                      AS rn,
                  net_id,
                  parent_net_id,
                  addr,
                  PMU.int2quad (ADDR) || '/' || NET_PREFIX_LENGTH
                      AS "NETWORK",
                  SUBSTR (PMU.int2quad (ADDR), 1, 15)
                      "IP_ADDRESS",
                     CONNECT_BY_ROOT PMU.int2quad (ADDR)
                  || '/'
                  || NET_PREFIX_LENGTH
                      AS "PARENT_NODE",
                  LEVEL
                      LVL,
                  SYS_CONNECT_BY_PATH (PMU.int2quad (ADDR), '/')
                      "TREE"
             FROM HIERARCHY_TAB
       START WITH net_id > 1 AND parent_net_id IS NULL
       CONNECT BY NOCYCLE PRIOR net_id = PARENT_NET_ID
ORDER SIBLINGS BY addr) where network='192.168.1.0/24'

+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| rn | net_id | parent_net_id | addr       | NETWORK          | IP_ADDRESS    | PARENT_NODE                  | LVL | TREE                                               |
+====+========+===============+============+==================+===============+==============================+=====+====================================================+
| 1  | 2      | NULL          | 3232235520 | 192.168.0.0/24   | 192.168.0.0   | 192.168.0.0/24               | 1   | /192.168.0.0                                       |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 2  | 6      | 2             | 3232235776 | 192.168.1.0/24   | 192.168.1.0   | 192.168.0.0/24               | 2   | /192.168.0.0/192.168.1.0                           |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 3  | 7      | 2             | 3232236032 | 192.168.2.0/24   | 192.168.2.0   | 192.168.0.0/24               | 2   | /192.168.0.0/192.168.2.0                           |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 4  | 11     | 6             | 3232236288 | 192.168.1.128/25 | 192.168.1.128 | 192.168.1.0/24               | 3   | /192.168.0.0/192.168.1.0/192.168.1.128             |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 5  | 12     | 6             | 3232236544 | 192.168.1.192/26 | 192.168.1.192 | 192.168.1.0/24               | 3   | /192.168.0.0/192.168.1.0/192.168.1.192             |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 6  | 8      | 7             | 3232236288 | 192.168.2.128/25 | 192.168.2.128 | 192.168.2.0/24               | 3   | /192.168.0.0/192.168.2.0/192.168.2.128             |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 7  | 9      | 7             | 3232236544 | 192.168.2.192/26 | 192.168.2.192 | 192.168.2.0/24               | 3   | /192.168.0.0/192.168.2.0/192.168.2.192             |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+
| 8  | 13     | 11            | 3232236304 | 192.168.1.0/26   | 192.168.1.0   | 192.168.1.0/24/192.168.1.128 | 4   | /192.168.0.0/192.168.1.0/192.168.1.128/192.168.1.0 |
+----+--------+---------------+------------+------------------+---------------+------------------------------+-----+----------------------------------------------------+

Question:
1. for a given network I need to get network record and previous row.
2. for a given network I need to get network level and all previous levels of records till its parent_node;

Thanks!
This post has been answered by Solomon Yakobson on Apr 10 2023
Jump to Answer
Comments
Post Details
Added on Apr 10 2023
#pl-sql, #sql
2 comments
420 views