Recursive query to explode a BOM

• phpons

  SSC Veteran

  Points: 287

  More actions

  July 2, 2014 at 1:42 am

  #292877

  Hi,

  I'm trying to use a recursive query to find path between assembly and parts.

  The BOM is similar to(I've limited the number of rows and lines):

  BOM NumberMat Number

  20000222001770

  20000222003496

  20000222001527

  20000222003495

  20002462002005

  20005062000246

  20008372000246

  20015272001528

  20015332000246

  20017702001771

  20017802000246

  20022922000506

  20023952002292

  20025652000837

  20025662002565

  20028752002566

  20034952002875

  20034962002395

  BOM Number 2000022 is head of the BOM

  I'm looking for all the path from 2000022 to 2002005. There are 4 path:

  2002005, 2000246, 2000506, 2002292, 2002395, 2003496, 2000022

  2002005, 2000246, 2000837, 2002565, 2002566, 2002875, 2003495, 2000022

  2002005, 2000246, 2001533, 2001528, 2001527, 2000022

  2002005, 2000246, 2001780, 2001772, 2001771, 2001770, 2000022

  I'm trying to use the following recursive query:

  WITH maBOM (PIECE, STEPS, CHEMIN)

  AS

  (SELECT DISTINCT BOM3.[BOM Number], 0, CAST('2000022' AS VARCHAR(MAX))

  FROM BOM3

  where [BOM Number]=2000022

  UNION ALL

  SELECT [Mat Number], b1.STEPS+1, cast(b1.CHEMIN as varchar(max))+', '+cast(b2.[Mat Number] as varchar(max))

  FROM BOM3 AS b2

  INNER JOIN maBOM AS b1

  ON b1.PIECE = b2.[BOM Number])

  SELECT *

  FROM maBOM

  where piece=2002005

  But this query will return only 2 path:

  2000022, 2003495, 2002875, 2002566, 2002565, 2000837, 2000246, 2002005

  2000022, 2003496, 2002395, 2002292, 2000506, 2000246, 2002005

  How should I modify it so that is returns the 4 path?

  Thank's a lot in advance,

  Philippe

• sivaj2k

  Ten Centuries

  Points: 1188

  More actions

  July 3, 2014 at 7:55 am

  #1726825

  Hi,

  As per your input the other sets should come like

  2000022, 2001770, 2001771

  2000022, 2001527, 2001528

  Above result set dosen't have 2002005. so no result

  -----

  declare @bom table ([BOM Number] varchar(max),[Mat Number] varchar(max))

  insert into @bom

  select '2000022' [BOM Number],'2001770' [Mat Number] union

  select '2000022','2003496' union

  select '2000022','2001527' union

  select '2000022','2003495' union

  select '2000246','2002005' union

  select '2000506','2000246' union

  select '2000837','2000246' union

  select '2001527','2001528' union

  select '2001533','2000246' union

  select '2001770','2001771' union

  select '2001780','2000246' union

  select '2002292','2000506' union

  select '2002395','2002292' union

  select '2002565','2000837' union

  select '2002566','2002565' union

  select '2002875','2002566' union

  select '2003495','2002875' union

  select '2003496','2002395'

  ;WITH maBOM (PIECE, STEPS, CHEMIN)

  AS

  (SELECT DISTINCT BOM3.[BOM Number], 0, CAST('2000022' AS VARCHAR(MAX))

  FROM @bom BOM3

  where [BOM Number]=2000022

  UNION ALL

  SELECT [Mat Number], b1.STEPS+1, cast(b1.CHEMIN as varchar(max))+', '+cast(b2.[Mat Number] as varchar(max))

  FROM @bom AS b2

  INNER JOIN maBOM AS b1

  ON b1.PIECE = b2.[BOM Number])

  select * into #temp from maBOM

  select * from #temp where piece=2002005

  select o.CHEMIN from #temp o join (

  select a.CHEMIN,max(b.STEPS) step

  from #temp a , #temp b where a.steps = 1 and

  b.CHEMIN like a.CHEMIN+'%'

  group by a.CHEMIN)b on o.STEPS = b.step and

  o.CHEMIN like b.CHEMIN+'%'

  drop table #temp

  Regards

  Siva Kumar J.