How to correctly order a recursive DB2 query

Hello friendly internet wizards.

I am attempting to extract a levelled bill of materials (BOM) from a dataset, running in DB2 on an AS400 server. I have constructed most of the query (with a lot of help from online resources), and this is what I have so far;

@set item = '10984'

WITH BOM (origin, PMPRNO, PMMTNO, BOM_Level, BOM_Path, IsCycle, IsLeaf) AS
    (SELECT CONNECT_BY_ROOT PMPRNO AS origin, PMPRNO, PMMTNO,
    LEVEL AS BOM_Level,
    SYS_CONNECT_BY_PATH(TRIM(PMMTNO), ' : ') BOM_Path,
    CONNECT_BY_ISCYCLE IsCycle,
    CONNECT_BY_ISLEAF IsLeaf
    FROM MPDMAT
    WHERE PMCONO = 405 AND PMFACI = 'M01' AND PMSTRT = 'STD'    
    START WITH PMPRNO = :item   
    CONNECT BY NOCYCLE PRIOR PMMTNO = PMPRNO)

SELECT 0 AS BOM_Level, '' AS BOM_Path, MMITNO AS Part_Number, MMITDS AS Part_Name, 
    IFSUNO AS Supplier_Number, IDSUNM AS Supplier_Name, IFSITE AS Supplier_Part_Number
FROM MITMAS
LEFT OUTER JOIN MITVEN ON MMCONO = IFCONO AND MMITNO = IFITNO AND IFSUNO <> 'ZGA'
LEFT OUTER JOIN CIDMAS ON MMCONO = IDCONO AND IDSUNO = IFSUNO
WHERE MMCONO = 405
AND MMITNO = :item

UNION ALL

SELECT BOM.BOM_Level, BOM_Path, BOM.PMMTNO AS Part_Number, MMITDS AS Part_Name, 
    IFSUNO AS Supplier_Number, IDSUNM AS Supplier_Name, IFSITE AS Supplier_Part_Number
FROM BOM
LEFT OUTER JOIN MITMAS ON MMCONO = 405 AND MMITNO = BOM.PMMTNO
LEFT OUTER JOIN MITVEN ON IFCONO = MMCONO AND IFITNO = MMITNO AND IFSUNO <> 'ZGA' AND MMMABU = '2'
LEFT OUTER JOIN CIDMAS ON MMCONO = IDCONO AND IDSUNO = IFSUNO
;

This is correctly extracting the components for a given item, as well as the sub-components (etc). Current data looks like this (I have stripped out some columns that aren't relevant to the issue); https://pastebin.com/LUnGKRqH

My issue is the order that the data is being presented in. As you can see in the pastebin above, the first column is the 'level' of the component. This starts with the parent item at level 0, and can theoretically go down as far as 99 levels. The path is also show there, so for example the second component 853021 tells us that it's a 2nd level component, the paths up to INST363 (shown later in the list as a 1st level), then up to the parent at level 0.

I would like for the output to show in path order (for lack of a better term). Therefore, after level 0, it should be showing the first level 1 component, and then immediately be going into it's level 2 components and so on, until no further level is found. Then at that point, it returns back up the path to the next valid record. I hope I have explained that adequately, but essentially the data should come out as;

Level	Path	Item
0		10984
1	: INST363	INST363
2	: INST363 : 853021	853021
1	: 21907	21907

Any help that can be provided would be very much appreciated! Thanks,

Solution

This is an interesting query. Frankly I am surprised it works as well as it does since it is not structured the way I usually structure queries with a recursive CTE. The main issue is that while you have the Union in there, it does not appear to be within the CTE portion of the query.

When I write a recursive CTE, it is generally structured like this:

with cte as (
  priming select
  union all
  secondary select)
select * from cte

So to get a BOM from an Item Master that looks something like:

CREATE TABLE item (
  ItemNo         Char(10) PRIMARY KEY,
  Description    Char(50));

INSERT INTO item 
  VALUES ('Item0', 'Root Item'),
         ('Item1a', 'Second Level Item'),
         ('Item1b', 'Another Second Level Item'),
         ('Item2a', 'Third Level Item');

and a linkage table like this:

CREATE TABLE linkage (
  PItem     Char(10),
  CItem     Char(10),
  Quantity  Dec(5,0),
  PRIMARY KEY (PItem, CItem));

INSERT INTO linkage
  VALUES ('Item0', 'Item1a', 2),
         ('Item0', 'Item1b', 3),
         ('Item1b', 'Item2a', 5)

The recursive CTE to list a BOM for 'Item0' looks like this:

WITH bom (Level, ItemNo, Description, Quantity)
  AS (
  -- Load BOM with root item
  SELECT 0,
         ItemNo,
         Description,
         1
    FROM Item 
    WHERE ItemNo = 'Item0'
  UNION ALL
  -- Retrieve all child items
  SELECT a.Level + 1,
         b.CItem,
         c.Description,
         a.Quantity * b.Quantity
    FROM bom a
    join linkage b ON b.pitem = a.itemno
    join item c ON c.itemno = b.citem)
  -- Set the list order
  SEARCH DEPTH FIRST BY itemno SET seq
-- List BOM 
SELECT * FROM bom
ORDER BY seq

Here are my results:

LEVEL	ITEMNO	DESCRIPTION	QUANTITY
0	Item0	Root Item	1
1	Item1a	Second Level Item	2
1	Item1b	Another Second Level Item	3
2	Item2a	Third Level Item	15

Notice the search clause, that generates a column named seq which you can use to sort the output either depth first or breadth first. Depth first is what you want here.

NOTE: This isn't necessarily an optimum query since the description is in the CTE, and that increases the size of the CTE result set without really adding anything to it that couldn't be added in the final select. But it does make things a bit simpler since the 'priming query' retrieves the description.

Note also: the column list on the with clause following BOM. This is there to remove the confusion that DB2 had with the expected column list when the explicit column list was omitted. It is not always necessary, but if DB2 complains about an invalid column list, this will fix it.