CODEWITHSUNDEEP
sqloracle-database
Ismael Hartmann
Ismael Hartmann

Reputation: 49

How do i make a hierarchical Oracle query?

i have the following data:

structure:

id parent_id
1 null
1.1 1
1.1.1 1.1
1.1.2 1.1
2 null
2.1 2
2.1.1 2.1
2.2 2
2.2.1 2.2

values:

id value
1.1.1 10
1.1.1 15
1.1.2 5
2.1.1 20
2.2.1 10

i want to bring the following result:

id value
1 30
1.1 30
1.1.1 25
1.1.2 5
2 30
2.1 20
2.1.1 20
2.2 10
2.2.1 10

i need a oracle query to bring me this result, any idea how to do this?

PS: i know how to solve this problem with subselect, however i need a more efficient way

Upvotes: 1

Views: 48

Answers (1)

MT0
MT0

Reputation: 167822

You can use:

SELECT id,
       SUM( value ) AS total
FROM   (
  SELECT CONNECT_BY_ROOT(s.id) AS id,
         v.value
  FROM   structure s
         LEFT OUTER JOIN structure_values v
         ON ( v.id = s.id )
  WHERE  v.value IS NOT NULL
  CONNECT BY PRIOR s.id = s.parent_id
)
GROUP BY id
ORDER BY id

Which, for the sample data:

CREATE TABLE structure ( id, parent_id ) AS
SELECT '1',     null  FROM DUAL UNION ALL
SELECT '1.1',   '1'   FROM DUAL UNION ALL
SELECT '1.1.1', '1.1' FROM DUAL UNION ALL
SELECT '1.1.2', '1.1' FROM DUAL UNION ALL
SELECT '2',     null  FROM DUAL UNION ALL
SELECT '2.1',   '2'   FROM DUAL UNION ALL
SELECT '2.1.1', '2.1' FROM DUAL UNION ALL
SELECT '2.2',   '2'   FROM DUAL UNION ALL
SELECT '2.2.1', '2.2' FROM DUAL;

CREATE TABLE structure_values ( id, value ) AS
SELECT '1.1.1', 10 FROM DUAL UNION ALL
SELECT '1.1.1', 15 FROM DUAL UNION ALL
SELECT '1.1.2',  5 FROM DUAL UNION ALL
SELECT '2.1.1', 20 FROM DUAL UNION ALL
SELECT '2.2.1', 10 FROM DUAL;

Outputs:

ID TOTAL
1 30
1.1 30
1.1.1 25
1.1.2 5
2 30
2.1 20
2.1.1 20
2.2 10
2.2.1 10

db<>fiddle here

Upvotes: 3

Related Questions