Re: SQL to generate a "indented" bill of material listing.

My original Query:

with rpl (Parent, Child, seq, qtyreq, effdate, disdate, level)as
( select root.Parent, root.Child, root.seq, root.qtyreq, root.effdate, root.disdate, 1
from kbomview root
where root.bprod = 'SM5696' And idate.idate(root.effdate) <= DATE(CURRENT_DATE)
and idate.idate(root.disdate) > DATE(CURRENT_DATE)
UNION ALL
select kid.Parent, kid.Child, kid.seq, kid.qtyreq, kid.effdate, kid.disdate, parent.level + 1
from rpl parent, kbomview kid
where parent.child = kid.bprod and idate.idate(kid.effdate) <= DATE(CURRENT_DATE)
and idate.idate(kid.disdate) > DATE(CURRENT_DATE)
)

select Parent, Child, seq, qtyreq, effdate, disdate, level
from rpl

-----------------------------------------------------------
After following the Infocenter link that Tom gave me I added:

SEARCH DEPTH FIRST BY seq SET ordcol
order by ordcol

as follows:

with rpl (Parent, Child, seq, qtyreq, effdate, disdate, level)as
( select root.Parent, root.Child, root.seq, root.qtyreq, root.effdate, root.disdate, 1
from kbomview root
where root.bprod = 'SM5696' And idate.idate(root.effdate) <= DATE(CURRENT_DATE)
and idate.idate(root.disdate) > DATE(CURRENT_DATE)

UNION ALL
select kid.Parent, kid.Child, kid.seq, kid.qtyreq, kid.effdate, kid.disdate, parent.level + 1
from rpl parent, kbomview kid
where parent.child = kid.bprod and idate.idate(kid.effdate) <= DATE(CURRENT_DATE)
and idate.idate(kid.disdate) > DATE(CURRENT_DATE)
)
SEARCH DEPTH FIRST BY seq SET ordcol

select Parent, Child, seq, qtyreq, effdate, disdate, level
from rpl
order by ordcol




THIS WORKED PERFECTLY!!!!!











On 4/10/2013 6:24 PM, CRPence wrote:

On 10 Apr 2013 12:52, Gqcy wrote:

we have found a quick method get generate a bill of material (BOM)
list in pure SQL, but we can't get it ordered in a "indented"
manner.

Share the query [a recursive CTE aka RCTE I presume?], and perhaps
someone can offer a specific tweak to make that happen.

http://archive.midrange.com/mapics-l/200905/msg00065.html
"... It is not quite in the order that I want though. ..."

"I think the SEARCH DEPTH FIRST clause is required for the recursive CTE
to properly expand the item are nested properly, and generate\establish
a sorting column.

http://www.google.com/#q=recursive+query+bom+explode+depth+first
www.ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_olap.pdf
..."

I figure we need to add a work file that will keep track of the
parent-child, level... but don't know how to build it...

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzrecurse.htm
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Queries -> select-statement -> common-table-expression
_i Recursion example: bill of materials i_
"Bill of materials (BOM) applications are a common requirement in many
business environments. To illustrate the capability of a recursive
common table expression for BOM applications, consider a table of parts
with associated subparts and the quantity of subparts required by the part.
...
The column LEVEL was introduced to count the levels from the original
part. In the initialization fullselect, the value for the LEVEL column
is initialized to 1. In the subsequent fullselect, the level from the
parent is incremented by 1.
..."

<<SNIP>>

Item 1
... Child item 1
........ a child of a child
... Child item 2
... etc...

For the indenting as shown, the SQL REPEAT scalar, perhaps.?

REPEAT('.', ItmLvl) /* generate number of periods per Item Level */

Note: repeat('.', 0) gives the empty-string, so if in the
initialization-fullselect the item level starts at zero, there will be
no periods preceding its output as a concatenation of that expression
with the item.