July 12, 2016 at 12:58 am
I have written SQL Code to completely explode all bill of materials :
This is the table data :
CREATE TABLE bomline(
itemno VARCHAR(6) NOT NULL
,compno VARCHAR(6) NOT NULL
,quantity NUMERIC(9,6) NOT NULL
,PRIMARY KEY(itemno,compno)
);
INSERT INTO bomline(itemno,compno,quantity) VALUES ('1000','1100',2.000000);
INSERT INTO bomline(itemno,compno,quantity) VALUES ('1100','1200',3.000000);
INSERT INTO bomline(itemno,compno,quantity) VALUES ('2000','2100',4.000000);
INSERT INTO bomline(itemno,compno,quantity) VALUES ('2100','2200',2.000000);
Here's my SQL Code :
WITH tmpbom([itemno], [compno], [quantity], [level])
AS (SELECT S1.[itemno],
S1.[compno],
CAST(S1.[quantity] AS NUMERIC),
1
FROM bomline AS S1
UNION ALL
SELECT S2.[itemno],
S2.[compno],
CAST(S2.[quantity] * TS.[quantity] AS NUMERIC),
TS.[level] + 1
FROM bomline AS S2
INNER JOIN tmpbom AS TS
ON S2.[itemno] = TS.[compno])
SELECT [itemno],
[compno],
Isnull(quantity, 0) AS [quantity]
FROM tmpbom
This is what I get at the moment :
+--------+---------+-----------+
| ItemNo | CompNo | Quantity |
+--------+---------+-----------+
| 1000 | 1100 | 2.000000 |
| 1100 | 1200 | 3.000000 |
| 2000 | 2100 | 4.000000 |
| 2100 | 2200 | 2.000000 |
| 2100 | 2200 | 8.000000 |
| 1100 | 1200 | 6.000000 |
+--------+---------+-----------+
I want to have this result where all child components are related to their top level ItemNos with accumulated quantity:
+--------+---------+-----------+
| ItemNo | CompNo | Quantity |
+--------+---------+-----------+
| 1000 | 1100 | 2.000000 |
| 1000 | 1200 | 6.000000 |
| 2000 | 2100 | 4.000000 |
| 2000 | 2200 | 8.000000 |
+--------+---------+-----------+
July 12, 2016 at 1:57 am
you need to adjust your anchor for recursive CTE to work. something like this:
WITH tmpbom ([itemno], [compno], [quantity], [level])
AS
(
SELECT S1.itemno, S1.compno, CAST(S1.[quantity] AS NUMERIC), 1
FROM bomline AS S1
WHERE NOT EXISTS (
SELECT 1
FROM bomline b
WHERE S1.itemno = b.compno
)
UNION ALL
SELECT cte.itemno, S2.compno, CAST(cte.quantity * S2.quantity AS NUMERIC), cte.[level] + 1
FROM tmpbom cte
JOIN bomline S2 on cte.compno = S2.itemno
)
SELECT * FROM tmpbom
ORDER BY itemno
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply