April 27, 2009 at 4:16 am
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
;
Any help would be very gratefully received.
April 27, 2009 at 4:33 am
I have done a bit more reading around and it looks like this finctionality isn't available in SQL Server 2000. Back to the drawing board.
April 27, 2009 at 7:55 am
vaughan_davies (4/27/2009)
The database runs on SQL Server 2000.
You would get better help by posting in the appropriate forum. Since you have a SQL 2000 question, one of the SQL 2000 forums would be the proper place (you posted this in the SQL 2005 forums).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply