Oracle CONNECT BY with multiple tables

Question

I have 4 tables containing my data:

Table COMP: definition of my component data
COMPID  | NAME      | DESCRIPTION
--------+-----------+------------
000123  | Comp. 1   | A44.123
000277  | Comp. 2   | A96.277
000528  | Comp. 3   | 1235287
001024  | Comp. 4   | Lollipop
004711  | Comp. 5   | Yippie

Table COMPLIST: containing the sub-components of each component
COMPID  | POS  | SUBCOMPID   | QUANTITY
--------+------+------------ +-----------
000123  | 1    | 000277      | 3
000123  | 2    | 000528      | 1
000528  | 1    | 004711      | 1

Table COMPSUPPLIER: definition of the components suppliers
COMPID  | SUPPLIER  | ORDERNUMBER
--------+-----------+-------------
000123  | Supp1     | A44.123
000277  | Supp1     | A96.277
000528  | Supp2     | 1235287
001024  | Supp2     | ux12v39
004711  | Supp1     | 123456

Table ASSEMBLY: definition of my assembly
ASSYID  | POS  | COMPID  | QUANTITY
--------+------+---------+----------
5021    | 1    | 000123  | 1
5021    | 2    | 001024  | 2

I want to get all components used in an assembly with their supplier and order number (Edited: added Position):

POS    | COMPID  | NAME    | SUPPLIER | ORDERNUMBER | QUANTITY
-------|---------+---------+----------+-------------+----------
1      | 000123  | Comp. 1 | Supp1    | A44.123     | 1
1.1    | 000277  | Comp. 2 | Supp1    | A96.277     | 3
1.2    | 000528  | Comp. 3 | Supp2    | 1235287     | 1
1.2.1  | 004711  | Comp. 5 | Supp1    | 123456      | 1
2      | 001024  | Comp. 4 | Supp2    | ux12v39     | 2

My idea was to use a SELECT in combination with CONNECT BY but I can't get it working right.

My current approach (Edited: updated with GurV's input):

SELECT c.COMPID, c.NAME, cs.SUPPLIER, cs.ORDERNUMBER
FROM COMP c
JOIN COMPSUPPLIER cs ON c.COMPID = cs.COMPID
WHERE c.COMPID in (
    SELECT COMPID
    FROM ASSEMBLY
    WHERE ASSYID = '5021'
    UNION ALL
    SELECT SUBCOMPID
    FROM COMPLIST
    CONNECT BY NOCYCLE PRIOR SUBCOMPID = COMPID
    START WITH COMPID in (
      SELECT COMPID
      FROM ASSEMBLY
      WHERE ASSYID = '5402')
);

With this I get all my sub components but not the position. Is it possible to get also the position column somehow?

Answer 1

A standard hierarchical query will work for this problem. I see in your desired output that you don't have a column for assyid; if you have more than one assembly in your business, that's a flaw. Also, I thought at some point you will want to compute the total quantity of a sub-component for an assembly (say, screws are used in component a and also in component b, both part of assembly 1000, and you would need the total number of screws); but, since you want to show everything "in its proper hierarchy" (as reflected in the pos column), it seems you aren't interested in that, at least in this query. That would be harder to do with a standard hierarchical query and easier to do in a recursive query, but that doesn't seem to be the case here.

The idea is to union all between complist and assembly, adding a flag column to use in the start with clause of the hierarchical query. Everything else is pretty standard.

with 
     comp ( compid, name, description ) as (
       select '000123', 'Comp. 1', 'A44.123'  from dual union all
       select '000277', 'Comp. 2', 'A96.277'  from dual union all
       select '000528', 'Comp. 3', '1235287'  from dual union all
       select '001024', 'Comp. 4', 'Lollipop' from dual union all
       select '004711', 'Comp. 5', 'Yippie'   from dual
     ),
     Complist ( compid, pos, subcompid, quantity ) as (
       select '000123', 1, '000277', 3 from dual union all
       select '000123', 2, '000528', 1 from dual union all
       select '000528', 1, '004711', 1 from dual
     ),
     compsupplier ( compid, supplier, ordernumber ) as (
       select '000123', 'Supp1', 'A44.123' from dual union all
       select '000277', 'Supp1', 'A96.277' from dual union all
       select '000528', 'Supp2', '1235287' from dual union all
       select '001024', 'Supp2', 'ux12v39' from dual union all
       select '004711', 'Supp1', '123456'  from dual
     ),
     assembly ( assyid, pos, compid, quantity ) as (
       select '5021', 1, '000123', 1 from dual union all
       select '5021', 2, '001024', 2 from dual
     )
select h.assyid, ltrim(h.pos, '.') as pos, h.compid,
       c.name, s.supplier, s.ordernumber, h.quantity
from   (
         select subcompid as compid, quantity, 
                connect_by_root compid as assyid,
                sys_connect_by_path(pos, '.') as pos
         from   ( select complist.*, 'f'  as flag from complist
                  union all
                  select assembly.*, null as flag from assembly
                )
         start with flag is null
         connect by compid = prior subcompid
       ) h
       left outer join comp         c on h.compid = c.compid
       left outer join compsupplier s on h.compid = s.compid
;

Output:

ASSYID POS      COMPID NAME    SUPPLIER ORDERNUMBER   QUANTITY
------ -------- ------ ------- -------- ----------- ----------
5021   1        000123 Comp. 1 Supp1    A44.123              1
5021   1.1      000277 Comp. 2 Supp1    A96.277              3
5021   1.2      000528 Comp. 3 Supp2    1235287              1
5021   1.2.1    004711 Comp. 5 Supp1    123456               1
5021   2        001024 Comp. 4 Supp2    ux12v39              2

5 rows selected.

Answer 2

If I'm following your logic, you can use recursive subquery factoring instead of a hierarchical query, which makes cycles etc. a bit easier to cope with:

with rcte (position, compid, name, supplier, ordernumber, quantity) as (
  select to_char(a.pos), a.compid, c.name, cs.supplier, cs.ordernumber, a.quantity
  from assembly a
  join compsupplier cs on cs.compid = a.compid
  join comp c on c.compid = cs.compid
  where a.assyid = 5021
  union all
  select rcte.position ||'.' || cl.pos, cl.subcompid, c.name,
    cs.supplier, cs.ordernumber, cl.quantity
  from rcte
  join complist cl on cl.compid = rcte.compid
  join compsupplier cs on cs.compid = cl.subcompid
  join comp c on c.compid = cs.compid  
)
select *
from rcte;

POSITION   COMPID NAME    SUPPL ORDERNU   QUANTITY
---------- ------ ------- ----- ------- ----------
1          000123 Comp. 1 Supp1 A44.123          1
2          001024 Comp. 4 Supp2 ux12v39          2
1.1        000277 Comp. 2 Supp1 A96.277          3
1.2        000528 Comp. 3 Supp2 1235287          1
1.2.1      004711 Comp. 5 Supp1 123456           1

The anchor member gets the first two rows direct from the assembly data, including the position from that table - that's essentially your original (pre-Gurv) query, plus the position.

The recursive member then looks at complist for each generated row's compid existing as a subcompid, and appends its position to the parent's while getting the other relevant data from the other tables.

If you want to preserve the order as you showed it in the question, you can add additional columns to the recursive CTE that track the original position and the level you're currently at (possibly with other info to break ties, if they are possible), and exclude those from the final select list:

with rcte (position, compid, name, supplier, ordernumber, quantity,
    order_by_1, order_by_2)
as (
  select to_char(a.pos), a.compid, c.name, cs.supplier, cs.ordernumber, a.quantity,
    a.pos, 1
  from assembly a
  join compsupplier cs on cs.compid = a.compid
  join comp c on c.compid = cs.compid
  where a.assyid = 5021
  union all
  select rcte.position ||'.' || cl.pos, cl.subcompid, c.name,
    cs.supplier, cs.ordernumber, cl.quantity,
    rcte.order_by_1, rcte.order_by_2 + 1
  from rcte
  join complist cl on cl.compid = rcte.compid
  join compsupplier cs on cs.compid = cl.subcompid
  join comp c on c.compid = cs.compid  
)
select position, compid, name, supplier, ordernumber, quantity
from rcte
order by order_by_1, order_by_2;

POSITION   COMPID NAME    SUPPL ORDERNU   QUANTITY
---------- ------ ------- ----- ------- ----------
1          000123 Comp. 1 Supp1 A44.123          1
1.1        000277 Comp. 2 Supp1 A96.277          3
1.2        000528 Comp. 3 Supp2 1235287          1
1.2.1      004711 Comp. 5 Supp1 123456           1
2          001024 Comp. 4 Supp2 ux12v39          2