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

> Source: https://netray.co/tools/recursive-bom-explosion-sql-cte-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds a recursive common table expression that explodes a multi-level BOM with extended quantities, plus a where-used query, using your own table and column names.

## Category

developer

## Audience

- ERP developer
- Data analyst
- Engineering manager

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### BOM table name

**Key:** table

**Type:** text

**Default:** bom_line

**Help:** Generic name. Replace with your ERP table or view.

#### Parent item column

**Key:** parent_col

**Type:** text

**Default:** parent_item

#### Component item column

**Key:** child_col

**Type:** text

**Default:** component_item

#### Quantity per column

**Key:** qty_col

**Type:** text

**Default:** qty_per

#### Effective-from column

**Key:** from_col

**Type:** text

**Default:** eff_from

#### Effective-to column

**Key:** to_col

**Type:** text

**Default:** eff_to

#### Top-level item

**Key:** root

**Type:** text

**Default:** ASM-1000

#### As-of date

**Key:** as_of

**Type:** date

**Default:** 2026-10-20

#### Max levels

**Key:** max_depth

**Type:** number

**Default:** 20

**Help:** Guards against circular BOMs.

**Blocks:**

#### PostgreSQL

**Body:** WITH RECURSIVE bom_tree AS (
  SELECT b.{{parent_col}} AS parent_item,
         b.{{child_col}} AS component_item,
         b.{{qty_col}} AS qty_per,
         b.{{qty_col}} AS ext_qty,
         1 AS lvl
  FROM {{table}} b
  WHERE b.{{parent_col}} = '{{root}}'
    AND b.{{from_col}} <= DATE '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > DATE '{{as_of}}')
  UNION ALL
  SELECT b.{{parent_col}}, b.{{child_col}}, b.{{qty_col}},
         t.ext_qty * b.{{qty_col}}, t.lvl + 1
  FROM {{table}} b
  JOIN bom_tree t ON b.{{parent_col}} = t.component_item
  WHERE t.lvl < {{max_depth}}
    AND b.{{from_col}} <= DATE '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > DATE '{{as_of}}')
)
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)

**Body:** WITH bom_tree AS (
  SELECT b.{{parent_col}} AS parent_item,
         b.{{child_col}} AS component_item,
         b.{{qty_col}} AS qty_per,
         CAST(b.{{qty_col}} AS decimal(18,6)) AS ext_qty,
         1 AS lvl
  FROM {{table}} b
  WHERE b.{{parent_col}} = '{{root}}'
    AND b.{{from_col}} <= CAST('{{as_of}}' AS date)
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > CAST('{{as_of}}' AS date))
  UNION ALL
  SELECT b.{{parent_col}}, b.{{child_col}}, b.{{qty_col}},
         CAST(t.ext_qty * b.{{qty_col}} AS decimal(18,6)), t.lvl + 1
  FROM {{table}} b
  JOIN bom_tree t ON b.{{parent_col}} = t.component_item
  WHERE t.lvl < {{max_depth}}
    AND b.{{from_col}} <= CAST('{{as_of}}' AS date)
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > CAST('{{as_of}}' AS date))
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item
OPTION (MAXRECURSION {{max_depth}});

#### Oracle

**Body:** WITH bom_tree (parent_item, component_item, qty_per, ext_qty, lvl) AS (
  SELECT b.{{parent_col}}, b.{{child_col}}, b.{{qty_col}}, b.{{qty_col}}, 1
  FROM {{table}} b
  WHERE b.{{parent_col}} = '{{root}}'
    AND b.{{from_col}} <= DATE '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > DATE '{{as_of}}')
  UNION ALL
  SELECT b.{{parent_col}}, b.{{child_col}}, b.{{qty_col}},
         t.ext_qty * b.{{qty_col}}, t.lvl + 1
  FROM {{table}} b
  JOIN bom_tree t ON b.{{parent_col}} = t.component_item
  WHERE t.lvl < {{max_depth}}
    AND b.{{from_col}} <= DATE '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > DATE '{{as_of}}')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

#### MySQL 8 and later

**Body:** WITH RECURSIVE bom_tree AS (
  SELECT b.{{parent_col}} AS parent_item,
         b.{{child_col}} AS component_item,
         b.{{qty_col}} AS qty_per,
         b.{{qty_col}} AS ext_qty,
         1 AS lvl
  FROM {{table}} b
  WHERE b.{{parent_col}} = '{{root}}'
    AND b.{{from_col}} <= '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > '{{as_of}}')
  UNION ALL
  SELECT b.{{parent_col}}, b.{{child_col}}, b.{{qty_col}},
         t.ext_qty * b.{{qty_col}}, t.lvl + 1
  FROM {{table}} b
  JOIN bom_tree t ON b.{{parent_col}} = t.component_item
  WHERE t.lvl < {{max_depth}}
    AND b.{{from_col}} <= '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > '{{as_of}}')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

#### Where-used (PostgreSQL, upward)

**Body:** WITH RECURSIVE where_used AS (
  SELECT b.{{parent_col}} AS parent_item, b.{{child_col}} AS component_item, 1 AS lvl
  FROM {{table}} b
  WHERE b.{{child_col}} = '{{root}}'
    AND b.{{from_col}} <= DATE '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > DATE '{{as_of}}')
  UNION ALL
  SELECT b.{{parent_col}}, b.{{child_col}}, w.lvl + 1
  FROM {{table}} b
  JOIN where_used w ON b.{{child_col}} = w.parent_item
  WHERE w.lvl < {{max_depth}}
    AND b.{{from_col}} <= DATE '{{as_of}}'
    AND (b.{{to_col}} IS NULL OR b.{{to_col}} > DATE '{{as_of}}')
)
SELECT DISTINCT parent_item, lvl FROM where_used ORDER BY lvl, parent_item;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### What is a recursive CTE?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

recursive CTE BOM explosion, multi level BOM SQL query, bill of materials recursive query, BOM explosion SQL server, where used query SQL, indented BOM SQL, extended quantity BOM SQL, BOM CTE PostgreSQL

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json