hierarchical Query in Snowflake

Question

I am trying to build the child relationship for the Bill of material Explosion in Snowflake with below dataset.How to get the levels for maintaining the parent & child relationship using connect by prior clause in snowflake.With the two table as input for this output

PPBOM PPMAT PMAT LEVEL COMPONENT 
bom1  1001  1001 1     1002 
bom1  1001  1001 1     1003 
bom1  1001  1001 1     1004 
bom1  1001  1001 1     1005 
bom1  1001  1002 2     1009 
bom1  1001  1002 2     1010 
bom1  1001  1009 3     1011 
bom1  1001  1009 3     1012

Answer 1

so you are wanting to use CONNECT BY, but the gap with my working how to root the answer to just the descendants of BOM1/1001, which the START WITH clase resolves, otherwise I was going to try a QUALIFY on SPLIT(path,':')[1] = 1001

WITH mast AS (
    SELECT * from values 
      ('bom1', 1001)
      ,('bom2', 2001)
      ,('bom3', 3001)
      ,('bom4', 4001)
      ,('bom5', 5001)
      ,('bom6', 6001)
      ,('bom7', 7001)
      ,('bom8', 8001)
      ,('bom9', 1002)
      ,('bom10', 1009)
      v(bom, material)
), stop as (
    SELECT * from values 
      ('bom1', 1002)
      ,('bom1', 1003)
      ,('bom1', 1004)
      ,('bom1', 1005)
      ,('bom2', 2002)
      ,('bom2', 2003)
      ,('bom2', 2004)
      ,('bom2', 2005)
      ,('bom2', 2006)
      ,('bom9', 1009)
      ,('bom9', 1010)
      ,('bom10', 1011)
      ,('bom10', 1012)
      v(bom, comp)
), hiearch AS (
    select pm.bom, pm.material as pmat, s.comp
    from mast pm
    join stop s on pm.bom = s.bom
)
select 
    --sys_connect_by_path(pmat, ':') as path,
    h.pmat, 
    level,
    h.comp
from hiearch h
start with pmat = 1001
connect by pmat = prior comp
order by pmat, comp

gives:

PMAT    LEVEL   COMP
1001    1   1002
1001    1   1003
1001    1   1004
1001    1   1005
1002    2   1009
1002    2   1010
1009    3   1011
1009    3   1012

thus if you want more complex root logic, you can do it via a where clause like so:

select * from (
  select 
      sys_connect_by_path(pmat, ':') as path,
      h.pmat, 
      level,
      h.comp
  from hiearch h
  connect by pmat = prior comp
  order by pmat, comp
)
where split(path,':')[1] = '1001'

with the updated requirement for the root node name/code to be included can be done via:

WITH hiearch AS (
    select pm.bom, pm.material as pmat, s.comp
    from mast pm
    join stop s on pm.bom = s.bom
)
select split(bom_path,':')[1] as bom
    ,split(pmat_path,':')[1]::number as ppmat
    ,pmat
    ,level
    ,comp
from (
  select 
      sys_connect_by_path(h.pmat, ':') as pmat_path,
      sys_connect_by_path(h.bom, ':') as bom_path,
      h.pmat, 
      level,
      h.comp
  from hiearch h
  start with pmat = 1001
  connect by pmat = prior comp
  order by pmat, comp
)
order by 1,3;