Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Mikael.
have you tryed Hierarchy() and HierarchyBelongsTo() clauses?
reg
D
Where you able to solve it? If so, could you share it?