Simple Recursive Query in Oracle

Simple recursive query in Oracle

You can use connect by clause.

In your case, SQL might look like:

select child, parent, level
from family_tree
connect by prior parent = child

Recursive query in Oracle

In Oracle this is easily done using CONNECT BY

select message_id, parent_id, message_content
from messages
start with message_id = 97 -- this is the root of your conversation
connect by prior message_id = parent_id;

This walks the tree from top to bottom.

If you want to walk the tree from a single message to the root, change the start with and the connect by part:

select message_id, parent_id, message_content
from messages
start with message_id = 100 -- this is the root of your conversation
connect by prior parent_id = message_id; -- this now goes "up" in the tree

Recursive query to get entire hierarchy [ORACLE]

Build the hierarchy for every row, returning the value for pn for the root rows.

You can do this with connect by using connect_by_root:

create table t (
c1 varchar2(4), c2 varchar2(4)
);

insert into t values ( 'ABC1', 'ABC2' );
insert into t values ( 'ABC1', 'ABC9' );
insert into t values ( 'ABC2', 'ABC3' );
insert into t values ( 'ABC3', 'ABC4' );
insert into t values ( 'DEF1', 'DEF2' );
insert into t values ( 'GHI1', 'GHI2' );
insert into t values ( 'GHI2', 'GHI1' );
commit;

select * from (
select distinct connect_by_root c1 rt, c2
from t
connect by nocycle c1 = prior c2
)
where rt <> c2
order by rt, c2;

RT C2
ABC1 ABC2
ABC1 ABC3
ABC1 ABC4
ABC1 ABC9
ABC2 ABC3
ABC2 ABC4
ABC3 ABC4
DEF1 DEF2
GHI1 GHI2
GHI2 GHI1

The nocycle clause detects loops and stops processing.

Or recursive with by selecting the value of the root on each iteration:

with tree ( c1, c2, rt ) as (
select c1, c2, c1 from t
union all
select t.c1, t.c2, tree.rt
from tree
join t
on t.c1 = tree.c2
) cycle c1 set is_cycle to 'Y' default 'N'
select rt, c2
from tree
where is_cycle = 'N'
and rt <> c2
order by rt, c2;

RT C2
ABC1 ABC2
ABC1 ABC3
ABC1 ABC4
ABC1 ABC9
ABC2 ABC3
ABC2 ABC4
ABC3 ABC4
DEF1 DEF2
GHI1 GHI2
GHI2 GHI1

The cycle clause detects loops.

Recursive query in Oracle until a parent in the hierarchy meets a condition?

You can use a recursive CTE for this:

with cte(id, value_id, parent_value_id) as (
select id, value_id, value_id as parent_value_id
from t
where value_id is not null
union all
select t.id, t.value_id, cte.parent_value_id
from cte join
t
on t.parent_id = cte.id
where t.value_id is null
)
select *
from cte
order by id;

Here is a db<>fiddle.

Oracle SQL/PLSQL: Hierarchical recursive query

I think you need to do this in two steps.

In step 1, you create the hierarchical query results and explode the quantities down to each level. For example, is you have a path like 100->210->320 and the qty values for those are 2, 3, and 5 respectively, then you have exploded quantities 2, 6, and 30, respectively.

Then, having that, you can multiply those exploded quantities by the unit code to get how much that row will contribute to the overall cost of all its parent levels. You can then summarize those costs with a scalar subquery.

Here is the query:

WITH c ( model_no, model_rev, lvl, p_seq_no, seq_no, unit_cost, qty, exploded_qty, pth) AS 
( SELECT model_no, model_rev, 1 lvl, p_seq_no, seq_no, unit_cost, qty, qty exploded_qty, to_char(seq_no) pth
FROM prod_cost_model_struct_tab
WHERE model_no = 1000
AND model_rev = 1
AND seq_no = 300
UNION ALL
SELECT child.model_no, child.model_rev, parent.lvl+1, child.p_seq_no, child.seq_no, child.unit_cost, child.qty, parent.exploded_qty * child.qty exploded_qty, parent.pth || '>' || child.seq_no pth
FROM prod_cost_model_struct_tab child INNER JOIN c parent
ON child.model_no = parent.model_no
AND child.model_rev = parent.model_rev
AND child.p_seq_no = parent.seq_no )
search depth first by seq_no set ord,
exploded_qtys AS (
SELECT lvl,
c.p_seq_no,
c.seq_no,
c.unit_cost,
c.qty,
c.exploded_qty,
c.pth,
c.ord,
case when (lvl - lead(lvl) over (order by ord)) < 0 then
-- It is not a leaf, level contributes nothing (assumption: see question)
-- QUESTION: What does the unit_cost represent on non-leaf rows?
0 ELSE c.exploded_qty * c.unit_cost END level_cost_contribution
FROM c )
SELECT e.*, ( SELECT nvl(sum(level_cost_contribution),0) FROM exploded_qtys e2 WHERE e2.pth LIKE e.pth || '>%' ) + level_cost_contribution aggregate_cost
FROM exploded_qtys e
+-----+----------+--------+-----------+-----+--------------+---------------+-----+-------------------------+----------------+
| LVL | P_SEQ_NO | SEQ_NO | UNIT_COST | QTY | EXPLODED_QTY | PTH | ORD | LEVEL_COST_CONTRIBUTION | AGGREGATE_COST |
+-----+----------+--------+-----------+-----+--------------+---------------+-----+-------------------------+----------------+
| 1 | 100 | 300 | 8889 | 1 | 1 | 300 | 1 | 0 | 8783 |
| 2 | 300 | 400 | 1701 | 1 | 1 | 300>400 | 2 | 1701 | 1701 |
| 2 | 300 | 500 | 970 | 1 | 1 | 300>500 | 3 | 970 | 970 |
| 2 | 300 | 600 | 7 | 2 | 2 | 300>600 | 4 | 14 | 14 |
| 2 | 300 | 700 | 1160 | 1 | 1 | 300>700 | 5 | 1160 | 1160 |
| 2 | 300 | 800 | 580 | 1 | 1 | 300>800 | 6 | 580 | 580 |
| 2 | 300 | 900 | 96 | 1 | 1 | 300>900 | 7 | 96 | 96 |
| 2 | 300 | 1000 | 350 | 1 | 1 | 300>1000 | 8 | 350 | 350 |
| 2 | 300 | 1100 | 59 | 4 | 4 | 300>1100 | 9 | 0 | 232 |
| 3 | 1100 | 1200 | 28 | 1 | 4 | 300>1100>1200 | 10 | 112 | 112 |
| 3 | 1100 | 1300 | 1 | 1 | 4 | 300>1100>1300 | 11 | 4 | 4 |
| 3 | 1100 | 1400 | 3 | 1 | 4 | 300>1100>1400 | 12 | 12 | 12 |
| 3 | 1100 | 1500 | 4 | 1 | 4 | 300>1100>1500 | 13 | 16 | 16 |
| 3 | 1100 | 1600 | 22 | 1 | 4 | 300>1100>1600 | 14 | 88 | 88 |
| 2 | 300 | 1700 | 219 | 1 | 1 | 300>1700 | 15 | 0 | 218 |
| 3 | 1700 | 1800 | 10 | 1 | 1 | 300>1700>1800 | 16 | 10 | 10 |
| 3 | 1700 | 1900 | 1 | 4 | 4 | 300>1700>1900 | 17 | 4 | 4 |
| 3 | 1700 | 2000 | 200 | 1 | 1 | 300>1700>2000 | 18 | 200 | 200 |
| 3 | 1700 | 2100 | 4 | 1 | 1 | 300>1700>2100 | 19 | 4 | 4 |
| 2 | 300 | 2200 | 1160 | 1 | 1 | 300>2200 | 20 | 1160 | 1160 |
| 2 | 300 | 2300 | 1 | 8 | 8 | 300>2300 | 21 | 8 | 8 |
| 2 | 300 | 2400 | 1 | 2 | 2 | 300>2400 | 22 | 2 | 2 |
| 2 | 300 | 2500 | 1 | 1 | 1 | 300>2500 | 23 | 1 | 1 |
| 2 | 300 | 2600 | 2 | 1 | 1 | 300>2600 | 24 | 2 | 2 |
| 2 | 300 | 2700 | 4 | 2 | 2 | 300>2700 | 25 | 8 | 8 |
| 2 | 300 | 2800 | 103 | 1 | 1 | 300>2800 | 26 | 103 | 103 |
| 2 | 300 | 2900 | 95 | 1 | 1 | 300>2900 | 27 | 95 | 95 |
| 2 | 300 | 3000 | 0 | 4 | 4 | 300>3000 | 28 | 0 | 0 |
| 2 | 300 | 3100 | 2 | 1 | 1 | 300>3100 | 29 | 2 | 2 |
| 2 | 300 | 3200 | 0 | 66 | 66 | 300>3200 | 30 | 0 | 0 |
| 2 | 300 | 3300 | 0 | 12 | 12 | 300>3300 | 31 | 0 | 0 |
| 2 | 300 | 3400 | 0 | 33 | 33 | 300>3400 | 32 | 0 | 0 |
| 2 | 300 | 3500 | 0 | 4 | 4 | 300>3500 | 33 | 0 | 0 |
| 2 | 300 | 3600 | 0 | 8 | 8 | 300>3600 | 34 | 0 | 0 |
| 2 | 300 | 3700 | 0 | 4 | 4 | 300>3700 | 35 | 0 | 0 |
| 2 | 300 | 3800 | 0 | 4 | 4 | 300>3800 | 36 | 0 | 0 |
| 2 | 300 | 3900 | 0 | 5 | 5 | 300>3900 | 37 | 0 | 0 |
| 2 | 300 | 4000 | 0 | 84 | 84 | 300>4000 | 38 | 0 | 0 |
| 2 | 300 | 4100 | 0 | 32 | 32 | 300>4100 | 39 | 0 | 0 |
| 2 | 300 | 4200 | 0 | 32 | 32 | 300>4200 | 40 | 0 | 0 |
| 2 | 300 | 4300 | 1 | 12 | 12 | 300>4300 | 41 | 12 | 12 |
| 2 | 300 | 4400 | 2 | 3 | 3 | 300>4400 | 42 | 6 | 6 |
| 2 | 300 | 4500 | 145 | 1 | 1 | 300>4500 | 43 | 145 | 145 |
| 2 | 300 | 4600 | 48 | 1 | 1 | 300>4600 | 44 | 48 | 48 |
| 2 | 300 | 4700 | 2 | 2 | 2 | 300>4700 | 45 | 4 | 4 |
| 2 | 300 | 4800 | 1846 | 1 | 1 | 300>4800 | 46 | 0 | 1832 |
| 3 | 4800 | 4900 | 169 | 3 | 3 | 300>4800>4900 | 47 | 507 | 507 |
| 3 | 4800 | 5000 | 30 | 1 | 1 | 300>4800>5000 | 48 | 30 | 30 |
| 3 | 4800 | 5100 | 17 | 1 | 1 | 300>4800>5100 | 49 | 17 | 17 |
| 3 | 4800 | 5200 | 169 | 3 | 3 | 300>4800>5200 | 50 | 507 | 507 |
| 3 | 4800 | 5300 | 5 | 1 | 1 | 300>4800>5300 | 51 | 5 | 5 |
| 3 | 4800 | 5400 | 320 | 1 | 1 | 300>4800>5400 | 52 | 320 | 320 |
| 3 | 4800 | 5500 | 25 | 2 | 2 | 300>4800>5500 | 53 | 50 | 50 |
| 3 | 4800 | 5600 | 5 | 4 | 4 | 300>4800>5600 | 54 | 20 | 20 |
| 3 | 4800 | 5700 | 18 | 3 | 3 | 300>4800>5700 | 55 | 54 | 54 |
| 3 | 4800 | 5800 | 139 | 2 | 2 | 300>4800>5800 | 56 | 278 | 278 |
| 3 | 4800 | 5900 | 8 | 2 | 2 | 300>4800>5900 | 57 | 16 | 16 |
| 3 | 4800 | 6000 | 9 | 1 | 1 | 300>4800>6000 | 58 | 9 | 9 |
| 3 | 4800 | 6100 | 19 | 1 | 1 | 300>4800>6100 | 59 | 19 | 19 |
| 2 | 300 | 6200 | 25 | 1 | 1 | 300>6200 | 60 | 25 | 25 |
| 2 | 300 | 6300 | 9 | 1 | 1 | 300>6300 | 61 | 9 | 9 |
+-----+----------+--------+-----------+-----+--------------+---------------+-----+-------------------------+----------------+

NOTE #1

You did not indicate what the unit_cost represents for rows that are not leaf nodes in the hierarchy. Do they add to the cost of their children somehow? That will change the answer.

How to get depth of recursive SQL using WITH rather than CONNECT BY in Oracle?

Here is how you can clone the LEVEL pseudo-column (to use for indentation) as well as the DEPTH FIRST ordering of hierarchical CONNECT BY queries when you use the recursive WITH clause. You may want to see what happens when you remove the SEARCH... clause after the recursive subquery and the ORDER BY clause at the end - see in what way the output is affected.

with r ( lvl, key, name, treehier ) as (
select 1, key, name, treehier
from tree
where treehier is null
union all
select r.lvl + 1, t.key, t.name, r.key
from r join tree t on r.key = t.treehier
)
search depth first by name set ord
select rpad(' ', 2 * (lvl - 1), ' ') || name as name, key, treehier
from r
order by ord
;

NAME KEY TREEHIER
------------------------------ ---------- ----------
Software 11
DB 55 11
MS-SQL 77 55
Oracle 66 55
OS 22 11
Linux 33 22
Windows 44 22

Then you can add the keyword DESC (for descending sort, instead of the default ascending sort) - in the ORDER BY clause, after ord (almost certainly NOT what you want!) or, after deleting DESC from the ORDER BY clause, add it after name in the SEARCH clause. You will see what difference WHERE you add the DESC keyword makes in the output. These are all options that will be available to you in the future, if needed. You may also want to experiment by using KEY instead of NAME in the SEARCH clause - see what changes that will cause.

Recursive SQL Calculation - Recursive Calculation Cannot Be Performed

A simple recursive query to get what you want is:

with
i (root_part_no, sub_part_no, sub_part_quant, lvl) as (
select
root_part_no, sub_part_no, sub_part_quant, 1
from tblbom where root_part_no like '132EE%' and isemptyind <> 'Yes'
union all -- anchor member above; recursive member below
select
p.root_part_no, p.sub_part_no, p.sub_part_quant, i.lvl + 1
from i
join tblbom p on p.root_part_no = i.sub_part_no
)
select *
from i
order by lvl, root_part_no, sub_part_no

Pay attention to the UNION ALL clause. It separates the anchor query -- run only once -- from the recursive query that is run multiple times for every new row that results from it, until it produces no new rows anymore. This way this query can walk multiple levels, not just 2 of them.

Edit:

I tested the query above with the following (made up) data, and I added the LVL column to show the recursive level:

create table tblbom (
root_part_no varchar2(10),
sub_part_no varchar2(10),
sub_part_quant number(6),
isemptyind varchar2(10) default 'No'
);

insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('132EER', '122FYY', 1);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('132EER', '766WWW', 2);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('122FYY', '849ZXA', 3);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('766WWW', '111111', 4);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('849ZXA', null, 5);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('111111', null, 6);

The Result (including level) is:

ROOT_PART_NO  SUB_PART_NO  SUB_PART_QUANT  LVL
------------ ----------- -------------- ---
132EER 122FYY 1 1
132EER 766WWW 2 1
122FYY 849ZXA 3 2
766WWW 111111 4 2
111111 <null> 6 3
849ZXA <null> 5 3


Related Topics



Leave a reply



Submit