Connect by in Oracle Sql

Connect by in Oracle SQL

CONNECT BY PRIOR empno = manager_id;

This will produce the recursion. All records that are part of the next lower hierarchical level will return. This will return a hierarchy from top to bottom for all managers and their respective under working subordinates.

30 (manager_id)
   12 
   5 (manager_id)
      1
      7
20 (manager_id)
   15
   10

Oracle Connect By

It seems cycles are the problem.

Cycles are detected by looking at the columns with the PRIOR operator in the CONNECT BY clause.

To break such cycles, you need to add conditions that are trivially true but use the PRIOR operator on additional columns.

For example: to your CONNECT BY clause, add

and prior emp_id is not null

(assuming that emp_id can never be null).

Display immediate parent after CONNECT_BY_ROOT in Oracle PL SQL

You can use conditional sys_connect_by_path:

rtrim(sys_connect_by_path(case when level<=2 then id else null end, '.'),'.') 

Oracle connect by including a stop criteria

You can use Recursive subquery factoring to stop all searching like this:

with h(it,art,match,anymatch) as
       (select item, assembly
             ,     case when attr1 = 'marker' then 1 else 0 end
             , max(case when attr1 = 'marker' then 1 else 0 end) over()
          from articles
         where component = '0815'
        union all
        select item, assembly
             ,     case when attr1 = 'marker' then 1 else 0 end
             , max(case when attr1 = 'marker' then 1 else 0 end) over()
          from h, articles
         where art = component
           and anymatch = 0)
cycle art set cycle to 1 default 0
select it item
  from h
 where match = 1
   and cycle = 0

It will return all matches that are found on a smallest possible level.

However as it is breadth first search, it will not be much faster if the first found marker is deep.

Changing condition anymatch = 0 to match = 0 (anymatch would not need to be calculated anymore) would stop only searching down the branch the match is on.

START WITH and CONNECT BY in Oracle SQL

Why not simply:

 SELECT level, * FROM accounts
 START WITH parent_account_id = account_id
 CONNECT BY PRIOR account_id = parent_account_id
         AND account_id <> parent_account_id

?

Oracle connect by prior along with max() over partition by to remove duplicate subtree

Wrap your recursive query in another CTE and filter on that:

WITH 
  test_hierarchy AS (
    SELECT 'a' parent, 'b' child FROM dual UNION ALL
    SELECT 'b','c' FROM dual UNION ALL
    SELECT 'd','e' FROM dual UNION ALL
    SELECT 'e','f' FROM dual UNION ALL
    SELECT 'f','g' FROM dual UNION ALL
    SELECT 'f','h' FROM dual
  ),
  recursion AS (
    SELECT
      parent,
      child,
      CONNECT_BY_ROOT child AS init_child,
      LEVEL AS lvl,
      CONNECT_BY_ISLEAF AS isleaf,
      MAX(LEVEL) OVER(
        PARTITION BY parent
      ) AS max_level
    FROM
      test_hierarchy
    START WITH child IN ('c', 'b', 'e', 'f', 'h', 'g')
    CONNECT BY PRIOR parent = child
  )
SELECT *
FROM recursion
WHERE isleaf = 1 AND lvl = max_level

---

Related Topics