Developer ToolsFree generatorPDF, Excel, Word export

Recursive BOM Explosion SQL Generator (CTE for PostgreSQL, SQL Server, Oracle, MySQL)

Multi-level BOM explosions and where-used lists are two of the most common queries in manufacturing data work. A recursive common table expression does both in a single statement. This generator writes the query for four common databases using the table and column names you give it.

Fill in

Generic name. Replace with your ERP table or view.

Guards against circular BOMs.

Generated code
Download:

PostgreSQL

WITH RECURSIVE bom_tree AS (
  SELECT b.parent_item AS parent_item,
         b.component_item AS component_item,
         b.qty_per AS qty_per,
         b.qty_per AS ext_qty,
         1 AS lvl
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         t.ext_qty * b.qty_per, t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

SQL Server (no RECURSIVE keyword)

WITH bom_tree AS (
  SELECT b.parent_item AS parent_item,
         b.component_item AS component_item,
         b.qty_per AS qty_per,
         CAST(b.qty_per AS decimal(18,6)) AS ext_qty,
         1 AS lvl
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= CAST('2026-10-20' AS date)
    AND (b.eff_to IS NULL OR b.eff_to > CAST('2026-10-20' AS date))
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         CAST(t.ext_qty * b.qty_per AS decimal(18,6)), t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= CAST('2026-10-20' AS date)
    AND (b.eff_to IS NULL OR b.eff_to > CAST('2026-10-20' AS date))
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item
OPTION (MAXRECURSION 20);

Oracle

WITH bom_tree (parent_item, component_item, qty_per, ext_qty, lvl) AS (
  SELECT b.parent_item, b.component_item, b.qty_per, b.qty_per, 1
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         t.ext_qty * b.qty_per, t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

MySQL 8 and later

WITH RECURSIVE bom_tree AS (
  SELECT b.parent_item AS parent_item,
         b.component_item AS component_item,
         b.qty_per AS qty_per,
         b.qty_per AS ext_qty,
         1 AS lvl
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         t.ext_qty * b.qty_per, t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > '2026-10-20')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

Where-used (PostgreSQL, upward)

WITH RECURSIVE where_used AS (
  SELECT b.parent_item AS parent_item, b.component_item AS component_item, 1 AS lvl
  FROM bom_line b
  WHERE b.component_item = 'ASM-1000'
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, w.lvl + 1
  FROM bom_line b
  JOIN where_used w ON b.component_item = w.parent_item
  WHERE w.lvl < 20
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
)
SELECT DISTINCT parent_item, lvl FROM where_used ORDER BY lvl, parent_item;

Table and column names are generic. Map them to your ERP BOM table or a view that exposes parent, component, quantity and effectivity. Most ERPs add scrap factors, yield, phantom handling, alternates and revision logic that this query does not apply, so extended quantity here is a plain product of quantities per. Test on a small assembly first and compare with the ERP's own BOM report. Run read-only queries on a replica when possible.

BOM query review

Optional help mapping this query to your actual ERP BOM tables and checking it against standard ERP reports.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter your BOM table or view and the column names for parent, component, quantity per and effectivity dates, then choose the top item, an as-of date and a maximum depth. Copy the block for your database. The query returns each level, the parent and component and the extended quantity, which is the quantity per multiplied down the path. Run it on a few assemblies you know well and compare to your ERP.

  • The first select is the anchor: direct components of the top item
  • The second select joins children to the rows found so far
  • The depth limit stops runaway loops from circular BOMs

How the recursion works

A recursive CTE starts with an anchor query and repeatedly joins the table to the previous result until no new rows appear. Each pass adds one BOM level. Extended quantity is carried forward by multiplying the parent's extended quantity by the component's quantity per. Syntax differs: PostgreSQL and MySQL need the RECURSIVE keyword, SQL Server and Oracle do not, and SQL Server enforces a default limit of 100 recursion levels unless you set MAXRECURSION.

Common mistakes

Forgetting an effectivity filter returns every historical revision at once and inflates quantities. Ignoring unit of measure differences between levels gives wrong totals. Phantom assemblies, scrap and yield factors, and alternate BOMs change the true requirement, and a plain product of quantities will not show them. Also remember that the same component can appear on several paths, so sum by component if you want a flat requirement. Add cycle detection if your data quality is uncertain.

Frequently Asked Questions

What is a recursive CTE?

A common table expression that references itself. It starts from an anchor query, then repeatedly applies a second query to the previous result until nothing new is returned. It is the standard SQL way to walk hierarchies like a bill of materials.

How do I flatten the result to total quantity per component?

Wrap the final select and group by component_item with a sum of ext_qty. If the same component appears at several levels or under several parents, the sum gives the total required per one top level unit, before scrap and yield.

Why does my query stop at 100 levels in SQL Server?

SQL Server has a default MAXRECURSION of 100. The generated SQL Server block sets the option to your maximum depth. A BOM deeper than 100 levels is rare and often signals a loop or a data problem worth checking.

Can I run this directly on my ERP database?

Only if you know the real table structure and your vendor and IT policies allow direct reads. Prefer a read replica or reporting view, never write to ERP tables with SQL, and confirm against the ERP's own BOM report.

Netray works with BOM and routing data in ERPs such as SyteLine, SAP, Oracle and others, and can talk through read-only query patterns and data checks.

Talk to an ExpertExplore More Free Tools

Related Tools

SAP

SAP BOM Where-Used SQL Generator

Fill in a component, plant and BOM usage to get SQL that lists every parent BOM using it, plus a query for BOM items with dependencies.

Developer Tools

BOM Low Level Code Python Script Generator

Fill in your CSV file and column names and get a Python script that assigns low level codes and flags circular BOMs.

Engineering & PLM

BOM Explosion Requirements Worksheet

Explode a bill of materials into component requirements, with quantity per, scrap allowance and extended cost for each line.

SAP

SAP Change Number BOM ABAP Select Generator

Generates an ABAP report snippet that lists BOM items changed under a given SAP change number, joining material-to-BOM, BOM item and change master data.

More Developer Tools tools

See all
Back to All Tools