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

If you are already on release 7.1 (TR3 or higher), you may also use the
Hierarchical Query Clause, which is much easier to understand and has a few
additional functions.
http://www.ibm.com/developerworks/ibmi/library/i-db2connectby/index.html

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gqcy
Gesendet: Thursday, 11.4 2013 20:19
An: midrange-l@xxxxxxxxxxxx
Betreff: 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/rbafzrecurs
e.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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.