Search notes:

Oracle SQL: start with … connect by example: recursive additions

This is a hopefully simple example to demonstrate start with … connect by statements.

Create the table

First, we create a table to hold data with a hierarchical relation ship. 
set feedback off

create table recursive_additions (
  parent     number,
  child      number
--constraint uq_tcb unique (child)
);

comment on table recursive_additions is 'The sum of all children with the same parent is equal to this parent';
Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/recursive-additions/create-table.sql

Fill the data

The relationshipt among the parent record and its child records is: the sum of all child values is equal to the value of their parent. 
--
-- 5 = 2+3
--
insert into recursive_additions values ( 5, 2);
insert into recursive_additions values ( 5, 3);

--
-- 18 = 11+7
--
insert into recursive_additions values (18,11);
insert into recursive_additions values (18, 7);

-- 
-- 17 = 9+8
-- 
insert into recursive_additions values (17, 9);
insert into recursive_additions values (17, 8);

-- 
-- 26 = 13+1+12
-- 
insert into recursive_additions values (26,13);
insert into recursive_additions values (26, 1);
insert into recursive_additions values (26,12);

-- 
-- 15=10+5
-- 
insert into recursive_additions values (15,10);
insert into recursive_additions values (15, 5);

--
-- 38=15+17+6
--
insert into recursive_additions values (38,15);
insert into recursive_additions values (38,17);
insert into recursive_additions values (38, 6);

--
-- 38, 26 and 18 have no parents (the parent is null)
--
insert into recursive_additions values (null, 38);
insert into recursive_additions values (null, 26);
insert into recursive_additions values (null, 18);
Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/recursive-additions/insert-data.sql

Select hierarchically

Now, we can execute the most basic start with … connect by sql statement. Note how start with clause establishes the condition for all root records and how the connect by clause establishes the recursion. 
select
  lpad(' ',2*(level-1)) || to_char(child) s 
from
  recursive_additions 
start with
   parent is null
connect by
  prior child = parent;
Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/recursive-additions/select-hierarchically.sql
The query returns 
18
  7
  11
26
  1
  12
  13
38
  6
  15
    5
      2
      3
    10
  17
    8
    9

Cleaning up

Finally, we can clean up. 
drop table recursive_additions purge;
Github repository oracle-patterns, path: /SQL/select/hierarchical_queries/start-with_connect-by/recursive-additions/clean-up.sql

See also

start with … connect by

Index