Search notes:

Oracle: Hierarchical queries - Common table expressions (CTE)

--
--  Prevent «ORA-32044: cycle detected while executing recursive WITH query»
--

create table tq84_cycle (
  me          varchar2(10),
  points_to   varchar2(10)
);


insert into tq84_cycle values ('ROOT'   ,  null );
insert into tq84_cycle values (  'foo'  , 'ROOT');
insert into tq84_cycle values (    '123', 'foo' );
insert into tq84_cycle values (    '456', 'foo' );
insert into tq84_cycle values (  'bar'  , 'ROOT');
insert into tq84_cycle values (    '789', 'bar' );
insert into tq84_cycle values (  'baz'  , 'ROOT');
insert into tq84_cycle values (    '000', 'baz' );

-- Troublemakers
insert into tq84_cycle values (  'bar'  , 'baz');
insert into tq84_cycle values (  'baz'  , 'bar');


with a (me, lvl) as (
    select
      me,
      0 lvl
    from 
      tq84_cycle 
    where 
      points_to is null
  union all
    select
      tq84_cycle.me,
      lvl + 1
    from 
      a join
      tq84_cycle on a.me = tq84_cycle.points_to
)
search depth first by me set column_name_for_ordering
cycle me set cycle_ to '1' default '0'
select
  lpad(' ', lvl) || me
--cycle_
from
  a;


drop table tq84_cycle purge;
Github repository Oracle-Patterns, path: /SQL/select/hierarchical_queries/common_table_expressions/cycle.sql
create table tq84_relations (
   item         varchar2(30) primary key,
   parent_item  varchar2(30) references tq84_relations
);


insert into tq84_relations values ('a-c'          , null);

insert into tq84_relations values(   'A'          , 'a-c');
insert into tq84_relations values(     'abc'      , 'A'  );
insert into tq84_relations values(     'def'      , 'A'  );
insert into tq84_relations values(     'ghi'      , 'A'  );

insert into tq84_relations values(   'B'          , 'a-c');
insert into tq84_relations values(     'jkl'      , 'B'  );

insert into tq84_relations values(   'C'          , 'a-c');
insert into tq84_relations values(     'mno'      , 'C'  );
insert into tq84_relations values(     'pqr'      , 'C'  );
insert into tq84_relations values(         '###'  , 'pqr');
insert into tq84_relations values(         '!!!'  , 'pqr');

insert into tq84_relations values ('d-e'          , null);

insert into tq84_relations values(   'D'          , 'd-e');

insert into tq84_relations values(   'E'          , 'd-e');
insert into tq84_relations values(     '123'      , 'E'  );
insert into tq84_relations values(     '456'      , 'E'  );


with recursion 
  (item, path)  -- Column alias list. Needed to prevent «ORA-32039: recursive WITH clause must have column alias list»
as (
  -- Anchor:
     select item, item from tq84_relations where parent_item is null 
  UNION ALL -- recursion: ------------------------------------
      select myself.item, parent.item || ' -> ' || myself.item 
      from recursion parent join 
           tq84_relations myself on parent.item = myself.parent_item
)
select path from recursion;

drop table tq84_relations;
Github repository Oracle-Patterns, path: /SQL/select/hierarchical_queries/common_table_expressions/ex_01.sql
create table tq84_relations (
   item         varchar2(30) primary key,
   parent_item  varchar2(30) references tq84_relations
);


insert into tq84_relations values ('a-c'          , null);

insert into tq84_relations values(   'A'          , 'a-c');
insert into tq84_relations values(     'abc'      , 'A'  );
insert into tq84_relations values(     'def'      , 'A'  );
insert into tq84_relations values(     'ghi'      , 'A'  );

insert into tq84_relations values(   'B'          , 'a-c');
insert into tq84_relations values(     'jkl'      , 'B'  );

insert into tq84_relations values(   'C'          , 'a-c');
insert into tq84_relations values(     'mno'      , 'C'  );
insert into tq84_relations values(     'pqr'      , 'C'  );
insert into tq84_relations values(         '###'  , 'pqr');
insert into tq84_relations values(         '!!!'  , 'pqr');

insert into tq84_relations values ('d-e'          , null);

insert into tq84_relations values(   'D'          , 'd-e');

insert into tq84_relations values(   'E'          , 'd-e');
insert into tq84_relations values(     '123'      , 'E'  );
insert into tq84_relations values(     '456'      , 'E'  );


with recursion 
  (item, path)  -- Column alias list. Needed to prevent «ORA-32039: recursive WITH clause must have column alias list»
as (
  -- Anchor:
     select item, item from tq84_relations where parent_item is null 
  UNION ALL -- recursion: ------------------------------------
      select myself.item, parent.item || ' -> ' || myself.item 
      from recursion parent join 
           tq84_relations myself on parent.item = myself.parent_item
)
-------------------------------------------------------
search depth first by item set column_name_for_ordering
-------------------------------------------------------
select 
  column_name_for_ordering, 
  path 
from recursion;

drop table tq84_relations;
Github repository Oracle-Patterns, path: /SQL/select/hierarchical_queries/common_table_expressions/search_depth_first.sql
create table tq84_hier_relation (
  id         number primary key,
  id_parent  references tq84_hier_relation
);

create table tq84_hier_description (
  id_rel  number, -- references tq84_hier_relation,
  lang    varchar2( 2),
  text    varchar2(30),
  --
  primary key(lang, text)
);

-- {

insert into tq84_hier_relation       values ( 1, null);
  insert into tq84_hier_relation     values ( 2,    1);
  insert into tq84_hier_relation     values ( 3,    1);
    insert into tq84_hier_relation   values ( 5,    3);
      insert into tq84_hier_relation values ( 7,    5);
    insert into tq84_hier_relation   values ( 6,    3);
  insert into tq84_hier_relation     values ( 4,    1);
insert into tq84_hier_relation       values ( 8, null);
  insert into tq84_hier_relation     values (10,    8);
    insert into tq84_hier_relation   values (11,   10);
    insert into tq84_hier_relation   values (12,   10);
  insert into tq84_hier_relation     values (13,    8);
insert into tq84_hier_relation       values ( 9, null);

--}

-- {


   insert into tq84_hier_description values ( 1, 'de',  'Wurzel A');
   insert into tq84_hier_description values ( 1, 'en',  'Root A'  );
   
   insert into tq84_hier_description values ( 8, 'de',  'Wurzel B');
   insert into tq84_hier_description values ( 8, 'en',  'Root B'  );
   
   insert into tq84_hier_description values ( 9, 'de',  'Wurzel C');
   insert into tq84_hier_description values ( 9, 'en',  'Root C'  );
   
-- insert into tq84_hier_description values ( 2, 'de',  'Stufe 1 A a');
   insert into tq84_hier_description values ( 2, 'en',  'Level 1 A a');
   
   insert into tq84_hier_description values ( 3, 'de',  'Stufe 1 A b');
-- insert into tq84_hier_description values ( 3, 'en',  'Level 1 A b');
   
-- insert into tq84_hier_description values ( 4, 'de',  'Stufe 1 A c');
-- insert into tq84_hier_description values ( 4, 'en',  'Level 1 A c');
   
   insert into tq84_hier_description values (10, 'de',  'Stufe 1 B d');
   insert into tq84_hier_description values (10, 'en',  'Level 1 B d');
   
   insert into tq84_hier_description values (13, 'de',  'Stufe 1 B e');
   insert into tq84_hier_description values (13, 'en',  'Level 1 B e');
   
   insert into tq84_hier_description values ( 5, 'de',  'Stufe 2 A b f');
   insert into tq84_hier_description values ( 5, 'en',  'Level 2 A b f');
   
-- insert into tq84_hier_description values ( 6, 'de',  'Stufe 2 A c g');
   insert into tq84_hier_description values ( 6, 'en',  'Level 2 A c g');
   
   insert into tq84_hier_description values (11, 'de',  'Stufe 2 B d i');
   insert into tq84_hier_description values (11, 'en',  'Level 2 B d i');
   
   insert into tq84_hier_description values (12, 'de',  'Stufe 2 B e j');
   insert into tq84_hier_description values (12, 'en',  'Level 2 B e j');
   
   insert into tq84_hier_description values ( 7, 'de',  'Stufe 3 B e j k');
   insert into tq84_hier_description values ( 7, 'en',  'Level 3 B e j k');

-- }

with descr as (
  select
    id,
    id_parent,
    text
  from
    tq84_hier_relation    rel                          left join
    tq84_hier_description des on rel.id = des.id_rel and
                                 des.lang = 'de'
),
hier (id, id_parent, text, lvl) as (
  select
    id,
    id_parent,
    text,
    0
  from
    descr
  where
    id_parent is null
union all
  select
    descr.id,
    descr.id_parent,
    descr.text,
    hier.lvl+1
  from
    hier                               join
    descr on hier.id = descr.id_parent
)
search depth first by id set xyz
select 
  substr(lpad(' ', 2*lvl) || nvl(text, '?'), 1, 50),
  xyz
from
  hier;

drop table tq84_hier_description purge;
drop table tq84_hier_relation    purge;
Github repository Oracle-Patterns, path: /SQL/select/hierarchical_queries/common_table_expressions/two_tables.sql

See also

Querying all_dependencies recursively.
Generate all possible substrings of a word (or text).
Hierarchical queries
Hierarchically summing up values
The error message ORA-32044: cycle detected while executing recursive WITH query

Index