I have a genealogy dataset e.g.
Parent: Batch A
Child: Batch B
Grandchild: Batch C & Batch D
Great grand children: Batch E, Batch F and Batch G
etc.
All nodes have associated variables (e.g. material no, quantity, description, etc.)
From this, which I want to extract a hierarchical data table (there are other variables included in the query, this is just a simplified version) e.g.
| LOT_NUMBER | SAP_MATL_NO | Child | COMP_ITEM | COMP_LOT |
|---|---|---|---|---|
| Batch A | Z123 | Batch B | Y123 | 1 |
| Batch B | Y123 | Batch C | X123 | 2 |
| Batch B | Y123 | Batch D | W123 | 2 |
| Batch C | X123 | Batch E | V123 | 3 |
| Batch C | X123 | Batch F | U123 | 3 |
| Batch D | W123 | Batch G | T123 | 3 |
I have this query which works perfectly for Batch A but I want to remove the hardcoding of Batch A in the query and parameterise it such that I can feed in a chosen value (or multiple values) for the original parent batch:
Select
P.SAP_PLANT_CODE,
PLI.SAP_MATL_NO,
PLI.ITEM_DESC as Parent_Desctiption,
PLI.ITEM_ID as Parent_Material_Type,
PDL.LOT_NUMBER,
LI.SAP_MATL_NO as COMP_ITEM,
LI.ITEM_DESC as Comp_Desctiption,
LI.ITEM_ID as Comp_Mat_Type,
DL.LOT_NUMBER as COMP_LOT,
FG.QTY_ISSD,
Level
from PGS_DW.FCT_GENEALOGY FG
INNER JOIN PGS_DW.DIM_LOT PDL on FG.LOT_KEY = PDL.LOT_KEY
INNER JOIN PGS_DW.LKUP_ITEM PLI on FG.ITEM_KEY = PLI.ITEM_KEY
INNER JOIN PGS_DW.LKUP_ITEM LI on FG.COMP_ITEM_KEY = LI.ITEM_KEY
INNER JOIN PGS_DW.DIM_LOT DL on FG.COMP_LOT_KEY = DL.LOT_KEY
INNER JOIN PGS_DW.DIM_SAP_PLANT_XREF P on PLI.SITE_KEY = P.SITE_KEY
Where LI.ITEM_ID in ('FERT', 'HALB')
START WITH PDL.LOT_NUMBER = 'BATCH A'
CONNECT BY PRIOR DL.LOT_NUMBER = PDL.LOT_NUMBER
Order by Level,PDL.LOT_NUMBER
I've tried changing the START WITH statement to numerous things e.g.
START WITH PDL.LOT_NUMBER IS NULL - returns no data
START WITH PDL.LOT_NUMBER = PDL.LOT_NUMBER - returns all data (~2.5m rows) and no hierarchy (i.e. everything is Level 1)