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

I'm having a hard time understanding how the output table is related to the two input tables. There doesn't seem to be a hierarchy - can you explain it in more detail? — Simon D
– Simon D, Commented May 7, 2020 at 8:31
yes more info needed. A wild guess level almost seems like from the example just a row_number() over MAST rows that will join to "STOP" — Simeon Pilgrim
– Simeon Pilgrim, Commented May 7, 2020 at 8:50
It's like One parent material divided into child component(material) as raw material for the parent material.MAST will have BOM & MATERIAL relationship and STOP will have for that BOM will have sub component as material.3 levels are there for the parent 1001. — Lesly Premsingh C
– Lesly Premsingh C, Commented May 7, 2020 at 8:58
ah, given your data is in multiple tables you will need to use Recursive CTE docs.snowflake.com/en/sql-reference/constructs/with.html — Simeon Pilgrim
– Simeon Pilgrim, Commented May 7, 2020 at 10:26
Its live Traversal query for each component into deeper level till it doesn't have split component for its.SAP BOM explosion is the sample i have given. — Lesly Premsingh C
– Lesly Premsingh C, Commented May 7, 2020 at 10:36

Simeon Pilgrim · Accepted Answer · 2020-05-07 19:39:27Z

3

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;

edited May 7, 2020 at 19:39

answered May 7, 2020 at 11:05

Simeon Pilgrim

26.7k3 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lesly Premsingh C Over a year ago

Hi Simeon, Thanks for your reply and its very helful .Just want to have 2 more columns append in front to have PBOM and PMAT which bom1 and 1001 to be stamped displayed for all the records since for this BOM and material entire bom was exploded\.PBOM PMAT bom1 1001 bom1 1001 bom1 1001 bom1 1001 bom1 1001 bom1 1001 bom1 1001 bom1 1001 can you reply how to do that in an hierarchical query to keep first root value for all those records...

Lesly Premsingh C Over a year ago

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

Simeon Pilgrim Over a year ago

again your ask, as it is written does not make much sense to me. But if you are wanting a column that has the root node, you will notice that I just happen to provided a second example that shows how to build the path, and extract the root item, thus you should be able to see have to select that split results. Also it often bad form to tack of extra question, once the question you asked has been answered.

Collectives™ on Stack Overflow

hierarchical Query in Snowflake

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related