I have a recursive query which outputs a BOM (Bill of Materials) and it does output all of the levels. however the problem is that it is out of order when the procedure is called this is in either access and SQL Server. And if there are no child parts, then the part is nothing is returned.
Code
Create/Alter table:
USE [<Database>]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE BOM
(
[POSITION] INT CHECK ([POSITION] >= 0),
[PARENT] VARCHAR(10),
[PART] VARCHAR(10),
[QUANTITY] INT NOT NULL
) ON [PRIMARY]
GO
Create/Alter procedure:
USE [<Database>]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: GETS BOM GIVEN PARAMETER
-- =============================================
CREATE PROCEDURE GETBOM
@TL VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON;
WITH TESTBOM AS
(
SELECT
[POSITION], [PARENT], [PART], [QUANTITY]
FROM
BOM
WHERE
[PART] = @TL
AND [Parent] IS NULL
UNION ALL
SELECT
E.[POSITION], E.[PARENT], E.[PART], E.[QUANTITY]
FROM
BOM E
INNER JOIN
TESTBOM M ON E.[PARENT] = M.[PART]
)
SELECT * FROM TESTBOM
END
GO
Sample data:
| POSITION | PARENT | PART | QUANTITY |
|---|---|---|---|
| 0 | A | 1 | |
| 1 | A | B | 1 |
| 0 | B | 1 | |
| 1 | B | E | 1 |
| 2 | B | F | 1 |
| 0 | F | 1 | |
| 1 | F | K | 1 |
| 2 | F | C | 1 |
| 0 | C | 1 | |
| 1 | C | G | 2 |
| 2 | C | H | 1 |
| 2 | A | C | 1 |
| 3 | A | D | 1 |
| 0 | D | 1 | |
| 1 | D | I | 1 |
| 0 | I | 1 | |
| 1 | I | L | 1 |
| 2 | I | M | 2 |
| 2 | D | J | 1 |
Outputs
A:
EXEC GETBOM 'A'
B:
EXEC GETBOM 'B'
C
EXEC GETBOM 'C'
D
EXEC GETBOM 'D'
E
EXEC GETBOM 'E'
CodePudding user response：
It's hard to say exactly what you need here without proper sample data, but it seems if you concatenate each item's position with all its parents, you can order by that.
CREATE OR ALTER PROCEDURE GETBOM
@TL VARCHAR(10)
AS
SET NOCOUNT ON;
WITH TESTBOM AS
(
SELECT POSITION, PARENT, PART, QUANTITY, CAST(POSITION AS nvarchar(100)) AS Ordering
FROM BOM
WHERE [PART] = @TL
and [Parent] is null
UNION ALL
SELECT E.[POSITION],E.[PARENT],E.[PART],E.[QUANTITY], CAST(CONCAT(M.Ordering, E.POSITION) AS nvarchar(200))
FROM BOM E
INNER JOIN TESTBOM M ON E.[PARENT]=M.[PART]
)
SELECT
POSITION, PARENT, PART, QUANTITY
FROM TESTBOM
ORDER BY
Ordering;