Hi.
I have created a recursive sql query for creating a BoM report.
Tryied to create a Hierarchy but dident succeeded
Any tipps ?
WITH SumBoM(LEVEL,PRODUCT, PART,SUBPART,QTY,SEQ, OP) AS
(
SELECT 1, ROOT.PMPRNO, ROOT.PMPRNO, ROOT.PMMTNO,ROOT.PMCNQT,
CAST(ROOT.PMMSEQ AS VARCHAR(70)) AS PMMSEQ, ROOT.PMOPNO
FROM MPDMAT ROOT
WHERE ROOT.PMCONO=999 AND ROOT.PMFACI='AAA' AND ROOT.PMPRNO='1111'
AND ROOT.PMSTRT='999'
UNION ALL
SELECT (PARENT.LEVEL + 1), PARENT.PRODUCT, CHILD.PMPRNO, CHILD.PMMTNO,
CHILD.PMCNQT,CAST((CAST (PARENT.SEQ as VARCHAR(30))+ '.' + cast(CHILD.PMMSEQ as VARCHAR(30)) ) AS VARCHAR(70)), CHILD.PMOPNO
FROM SumBoM PARENT, MPDMAT CHILD
WHERE PARENT.SUBPART = CHILD.PMPRNO AND CHILD.PMCONO=999 AND
CHILD.PMFACI='AAA' AND CHILD.PMSTRT='999' AND PARENT.LEVEL < 20
)
SELECT (ROW_NUMBER() OVER(ORDER BY SEQ)) AS SEQNO,
--RIGHT('......' + LEVEL, LEVEL) AS LEVEL ,PRODUCT,
RIGHT( LEVEL, LEVEL) AS LEVEL ,PRODUCT,
PRODITM.MMITDS AS PRODNAME,PART, OP,
PARTITM.MMITDS AS PARTNAME, SUBPART, SUBITM.MMITDS AS SUBNAME, QTY
FROM SumBoM
