How to get around Recursive member of a common table expression has multiple recursive references?

Viewed 89

I have the following two tables:

IF OBJECT_ID('tempdb.dbo.##BillsDetails', 'U') IS NOT NULL
  DROP TABLE ##BillsDetails; 
  IF OBJECT_ID('tempdb.dbo.##Material', 'U') IS NOT NULL
  DROP TABLE ##Material; 
CREATE TABLE ##BillsDetails
( ID int PRIMARY KEY NOT NULL,
 ParentID varchar(50) NULL,
 ChildID  varchar(50) NULL,
 Quantity  float NULL)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (1, 68,34, 10)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (2, 68,86, 13)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (3, 34,31, 7)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (4, 31,42, 100)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (5, 31,44, 56)
   INSERT INTO ##BillsDetails (ID, ParentID, ChildID,Quantity) VALUES (6, 44,57, 10)
   CREATE TABLE ##Material
( MaterialID int PRIMARY KEY NOT NULL,
MaterialName varchar(500) NULL)
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 68,'Closet')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 34,'Closet Door')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 86,'Shelf')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 31,'Rod')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 42,'Screw 142')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 44,'Screw 144')
INSERT INTO ##Material (MaterialID, MaterialName) VALUES ( 57,'iron')

##BillsDetails Contains for each material, the list of its composing materials (ChildID) and also its necessary quantity, I made the following recursive query to get all children of a given material and their children and to get for each of those materials, it's Total quantity, the total quantity of a material= it's own quantity * Total Quantity of it's parent.

Declare @IDmaterial int
set @IDmaterial= 68;
with JoinCTE AS
(
select det.ID, det.ParentID, det.ChildID, det.Quantity, M.MaterialName, 1 as [level], det.Quantity as TotalQuantity
from ##BillsDetails det
Left Join ##Material  M on det.ChildID= M.MaterialID
),
BillsCTE as(
select ID, ParentID, ChildID, Quantity, MaterialName, 1 as [level], TotalQuantity
From JoinCTE
where ParentID=@IDmaterial
UNION ALL
Select A.ID, A.ParentID, A.ChildID, A.Quantity, A.MaterialName, BillsCTE.[level]+1, 
(select A.Quantity*B.TotalQuantity from BillsCTE B where A.ChildID= B.ParentID)
  as TotalQuantity
from JoinCTE A 
inner join BillsCTE  on A.ParentID=BillsCTE.ChildID
)
select * from BillsCTE

This subquery

(select A.Quantity*B.TotalQuantity from BillsCTE B where A.ChildID= B.ParentID)

Returns the following error

Recursive member of a common table expression has multiple recursive references

How to calculate the Total quantity without referencing BillsCTE ?

Edit: expected output:

ID ParentID ChildID Quantity MaterialName level TotalQuantity
1  68       34      10       Closet Door  1     10 (level 1 child=>TotalQ=Q)
2  68       86      13       Shelf        1     13
3  34       31      7        Rod          2     70 (7*10)
4  31       42      100      Screw 142    3     71000(100*70)
5  31       44      56       Screw 144    3     3920 (70*56)
6  44       57      10       iron         4     39200
0 Answers
Related