Oracle CONNECT BY query. How to display initial condition

Question

I would like to use a query that lists a hierarchical structure:

 SELECT LEVEL, PART_NO, COMPONENT_PART, QTY_PER_ASSEMBLY
   FROM MANUF_STRUCTURE 
CONNECT 
     BY PRIOR COMPONENT_PART = PART_NO AND 
        PRIOR CONTRACT = CONTRACT AND 
        PRIOR BOM_TYPE_DB = BOM_TYPE_DB 
        START WITH PART_NO = '&PN' ORDER SIBLINGS BY LINE_ITEM_NO;

I would like to put the initial condition (in this example the '&PN') into the first column of the listing. How can I do that?

My goal would be to run this query with multiply initial conditions and list the corresponding initial condition as well.

Do you have any ideas?

Thanks,

Answer 1

You can use the operator CONNECT_BY_ROOT:

Documentation: http://docs.oracle.com/database/121/SQLRF/operators004.htm#SQLRF51162

Examples from the docs: http://docs.oracle.com/database/121/SQLRF/queries003.htm#i2069380

So you could do something like:

SELECT CONNECT_BY_ROOT PART_NO as ROOT_PART_NO,
       LEVEL, PART_NO, COMPONENT_PART, QTY_PER_ASSEMBLY
  FROM MANUF_STRUCTURE 
CONNECT BY
   PRIOR COMPONENT_PART = PART_NO AND 
   PRIOR CONTRACT = CONTRACT AND 
   PRIOR BOM_TYPE_DB = BOM_TYPE_DB 
START WITH PART_NO IN ('&PN1', '&PN2', '&PN3')
ORDER SIBLINGS BY LINE_ITEM_NO;

Answer 2

try this

SELECT '&PN' initial_condition, LEVEL, PART_NO, COMPONENT_PART, QTY_PER_ASSEMBLY
   FROM MANUF_STRUCTURE 
 CONNECT 
     BY PRIOR COMPONENT_PART = PART_NO AND 
        PRIOR CONTRACT = CONTRACT AND 
        PRIOR BOM_TYPE_DB = BOM_TYPE_DB 
        START WITH PART_NO = '&PN' ORDER SIBLINGS BY LINE_ITEM_NO;