SQLTeam.com | Weblogs | Forums

SQL Recursive query for Sage 200


#1

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. It fails on the BR.Reference field with the message: The multi-part identifier "BR.Reference" could not be bound.

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 BCL, BomRecord BR, 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 ?


#2

don't embed the code in VBA. Put your query in a view and select from the view in your VBA