Recursive SQL query

[krissz]

I have written an SQL query to bring BOM data into Excel from Sage 200.
I am now trying to make the query recursive so that I can have a single listing of all the components used in a build.
This will update an existing spreadsheet which brought in all this data from our older system

The key is BomComponentLine.BomComponentLineTypeID. If the item is a component, this equals 0; if it is a sub-assembly, this equals 2. I want to keep digging until all the items are components. The first problem is with the FROM statement after the UNION ALL; the aliases are not liked.

WITH FullBom AS
 (
 SELECT
 BomRecord.Reference, BomComponentLine.SequenceNumber, BomComponentLine.StockCode, BomComponentLine.Description,
 BomComponentLine.Quantity, BomComponentLine.Instructions, StockItem.AverageBuyingPrice, StockItem.StandardCost

 FROM
  BomBuildPackage BomBuildPackage, BomComponentLine BomComponentLine, BomComponentLineType BomComponentLineType,
  BomRecord BomRecord, StockItem StockItem

WHERE
 BomBuildPackage.BomBuildPackageID = BomComponentLine.BomBuildPackageID
  AND BomBuildPackage.BomRecordID = BomRecord.BomRecordID
  AND StockItem.Code = BomComponentLine.StockCode 
  AND BomComponentLineType.BomComponentLineTypeID = BomComponentLine.BomComponentLineTypeID 
  AND ((BomRecord.Reference='309/0201/s52r'))
 
 -- ORDER BY BomRecord.Reference
 -- Works perfectly to here -------------------------------------------------

  AND BomComponentLine.BomComponentLineTypeID = 0
 
 UNION ALL

 SELECT
 BR.Reference, BCL.SequenceNumber, BCL.StockCode, BCL.Description, BCL.Quantity, BCL.Instructions, SI.AverageBuyingPrice, SI.StandardCost

 FROM
  BomComponentLine BomComponentLine BCL, BomRecord BomRecord BR, StockItem StockItem  SI

INNER JOIN FullBom AS FB ON  BR.Reference = FB.StockCode
)

SELECT * FROM FullBom

ORDER BY BomRecord.Reference

Once I have it working, I will be embedding the code into VBA with the BomRecord.Reference being picked up as a reference.
What have I done wrong ?

 

[WBenhart]

To get a BOM and all of the sub components, try working with this query, it should put you in the right direction.

	WITH rpl (Parent, Child, seq, QTY, level) AS
		 ( 
			 SELECT 
				  root.Parent
				 ,root.PARTNUMBER
				 ,root.seq
				 ,root.QTY
				 ,1 
			 FROM dbo.BOM root 
			 WHERE 
			   root.PARENT = '23065-013'
		UNION ALL
			 SELECT 
				 kid.Parent
				,kid.PARTNUMBER
				,kid.seq
				,kid.QTY
				,level + 1 
			 FROM 
			   rpl parent INNER JOIN
			   dbo.BOM kid ON parent.Child = kid.parent
		 )
	 SELECT Parent, seq, Child, QTY, level
	 FROM rpl
	 ORDER BY 
		level DESC,
		Parent,
		seq

 

[krissz]

Hi
Thank you for your suggestion. I had explored this route before but it would not work - I did not have a defined set of levels, just 2.
I have now resolved this in a different way.
Thanks anyway