Can Hierarchical queries be used in a condition?

Question

I am going to build a Hierarchial Tree in Oracle Forms using the Hiearchical queries. As far as I know, the HQ works only for single table, can they be used in a condition where we have Master Details scenario.

Or can it be based on a view?

For example; I want to display an hierarchical with 3 of options from various tables. For example a Customer Order can have have various Items. First List of All CO, on clicking a specific CO, All the Items therein, An Item may have Sub Parts, and so on. Spanning multiple connected tables

Answer 1

If you have the test data:

CREATE TABLE customer_orders ( id, name ) AS
SELECT 1, 'Cust. Ord. XYZ' FrOM DUAL UNION ALL
SELECT 2, 'Cust. Ord. ABC' FrOM DUAL UNION ALL
SELECT 3, 'Cust. Ord. MNO' FrOM DUAL;

CREATE TABLE customer_order_items ( id, order_id, name ) AS
SELECT 1, 3, 'MNO_NNN' FROM DUAL UNION ALL
SELECT 2, 3, 'MNO_OOO' FROM DUAL UNION ALL
SELECT 3, 3, 'MNO_MMM' FROM DUAL UNION ALL
SELECT 4, 2, 'ABC_AAA' FROM DUAL;

CREATE TABLE customer_order_item_parts ( id, item_id, name ) AS
SELECT 1, 1, 'Part_2_N' FROM DUAL UNION ALL
SELECT 2, 1, 'Part_1_N' FROM DUAL UNION ALL
SELECT 3, 2, 'Part_1_O' FROM DUAL UNION ALL
SELECT 4, 2, 'Part_3_O' FROM DUAL UNION ALL
SELECT 5, 4, 'Part_5_A' FROM DUAL UNION ALL
SELECT 6, 4, 'Part_3_A' FROM DUAL UNION ALL
SELECT 7, 4, 'Part_2_A' FROM DUAL UNION ALL
SELECT 8, 4, 'Part_4_A' FROM DUAL;

Then you can use UNION ALL (and JOINs as required) to concatenate the data and once it is all compiled then you can apply a hierarchical query:

SELECT *
FROM   (
  SELECT id AS order_id,
         NULL AS item_id,
         NULL AS part_id,
         name
  FROM   customer_orders
  UNION ALL
  SELECT order_id,
         id,
         NULL,
         name
  FROM   customer_order_items
  UNION ALL
  SELECT i.order_id,
         p.item_id,
         p.id,
         p.name
  FROM   customer_order_items i
         INNER JOIN
         customer_order_item_parts p
         ON ( i.id = p.item_id )
)
START WITH item_id IS NULL
CONNECT BY NOCYCLE
      PRIOR order_id = order_id
AND   (  (   PRIOR item_id IS NULL
         AND part_id IS NULL )
      OR (   PRIOR part_id IS NULL
         AND part_id IS NOT NULL
         AND PRIOR item_id = item_id )
      )
ORDER SIBLINGS BY name

Which outputs:

ORDER_ID | ITEM_ID | PART_ID | NAME          
-------: | ------: | ------: | :-------------
       2 |    null |    null | Cust. Ord. ABC
       2 |       4 |    null | ABC_AAA       
       2 |       4 |       7 | Part_2_A      
       2 |       4 |       6 | Part_3_A      
       2 |       4 |       8 | Part_4_A      
       2 |       4 |       5 | Part_5_A      
       3 |    null |    null | Cust. Ord. MNO
       3 |       3 |    null | MNO_MMM       
       3 |       1 |    null | MNO_NNN       
       3 |       1 |       2 | Part_1_N      
       3 |       1 |       1 | Part_2_N      
       3 |       2 |    null | MNO_OOO       
       3 |       2 |       3 | Part_1_O      
       3 |       2 |       4 | Part_3_O      
       1 |    null |    null | Cust. Ord. XYZ

db<>fiddle here