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

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.