April 27, 2009 at 8:35 am
--- Posted this in the wrong forum before ---
I'm trying to run a recursive query on my bill of materials data. I have tried running the following, but consistently get a "invalid syntax near keyword 'WITH'" error. The database runs on SQL Server 2000.
WITH rbm(
level,
warehouse,
product_code,
component_whouse,
component_code,
usage_quantity
)
AS
(
SELECT
level= 1,
warehouse = root.warehouse,
product_code= root.product_code,
component_whouse= root.component_whouse,
component_code= root.component_code,
usage_quantity= root.usage_quantity
FROM
scheme.bmassdm AS root
UNION ALL
SELECT
level= parent.level + 1,
warehouse = parent.warehouse,
product_code= parent.product_code,
component_whouse= child.component_whouse,
component_code= child.component_code,
usage_quantity= parent.usage_quantity*child.usage_quantity
FROM
rbm AS parent,
scheme.bmassdm AS child
WHERE
parent.component_whouse = child.warehouse
AND parent.component_code = child.product_code AND level < 6
)
SELECT
warehouse = rbm.warehouse,
product_code= rbm.product_code,
component_whouse= rbm.component_whouse,
component_code= rbm.component_code,
usage_quantity= Sum(rbm.usage_quantity)
FROM
rbm
GROUP BY
rbm.warehouse,
rbm.product_code,
rbm.component_whouse,
rbm.component_code
ORDER BY
rbm.warehouse,
rbm.product_code
;[/code]
I guess this may not work with SQL2000, but if that is the case how would I achieve what I want to do - i.e. recursively sum the hierarchical bill of materials quantities?
Any help would be very gratefully received.
April 27, 2009 at 8:48 am
CTEs were only introduced with SQL 2005 so this code witll not work on 2k.
You should be able to re-write the code to use temp tables instead of CTEs to work on 2k
April 28, 2009 at 7:43 am
Right. Thanks. I'll give that a try
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply