Recursive BAQs

Tim Shoemaker
Principal Consultant, Epicor Software
March 11, 2021
Agenda

Introduction 01

Definitions 02

Illustration 03

Helpful Resources 04

Sample Build 05

Q&A 06

2
Introduction

Who Am I?

3
Introduction

Tim Shoemaker - Principal Consultant

Epicor Employee since Aug 1999

• Technical Support first 5 years
• Professional Services team since Dec 2004
• Prior to Epicor – an Epicor Avanté Customer
• Presented at Epicor Insights at least 12 years, attended as user 1 year
• Present at numerous User Group meetings

Focus:
• ERP Manufacturing Operations
• Engineering, MRP, Production, Inventory, Purchasing, Sales, etc
• Technology
• Product Configurator, BPMs, Queries, Dashboards, Data Migrations

4
Definitions

What are we talking about

5
Definition

Recursive Query: (Found on the web – Sherlock would be proud):

• Recursive queries are used to query hierarchical data

More Important to today:

• Recursive Queries are those that are Multi-Level queries using the same
data tables.

• Examples of where you need a Recursive Query:

• Bill Of Material
• Job Structure
• Employee structure (Manager, Supervisor, Lead, Shop Floor worker)
• Menu Structure

6
Definition

CTE Query:
• Common Table Expressions query
• Creates a temporary table
• Can be used as a sub-query (similar to an inner-sub query)
• Only type of sub-query that can be recursive by using a UnionAll query

• UnionAll query:
• A sub-query that unions all values from previous queries
• All columns MUST return identical value types
• Must have a TOP query that uses it

7
Illustrations
(& Screenshots)

A Picture is worth a thousand words

8
Recursive Illustrated

Level 0 Level 1 Level 2

(CTE) (UnionAll) (UnionAll)

Material 1
Assy 2
Material 2
Assy 1
Assy3 Material 3

9
Recursive Illustrated CTE
Query

Source:
https://medium.com/swlh/rec
ursion-in-sql-explained-
graphically-679f6a0f143b

UnionALL
Query

TOP
Query

10
Recursive Screenshots

11
Recursive Screenshots

12
 Recursive Screenshots – The SQL we will build
with [ctequery] as (
select
[anchor].[MenuID] as [anchor_MenuID],
[anchor].[MenuDesc] as [anchor_MenuDesc],
(cast (anchor.MenuID as nvarchar(500))) as [Calculated_SortOrder],
(0) as [Calculated_level]
from
Ice.Menu as anchor
where
(anchor.ParentMenuID = '')
union all
select
[childMenus].[MenuID] as [childMenus_MenuID],
[childMenus].[MenuDesc] as [childMenus_MenuDesc],
(cast (ctequery.Calculated_SortOrder + ', ' + right('0000' + cast(childMenus.Sequence as varchar(4)),4) as nvarchar(500))) as [Calculated_SortOrder],
(ctequery.Calculated_level + 1) as [Calculated_level]
from
ctequery as ctequery
inner join Ice.Menu as childMenus on childMenus.ParentMenuID = ctequery.anchor_MenuID)

select
[ctequery1].[anchor_MenuID] as [anchor_MenuID],
[ctequery1].[anchor_MenuDesc] as [anchor_MenuDesc],
[ctequery1].[Calculated_SortOrder] as [Calculated_SortOrder],
[ctequery1].[Calculated_level] as [Calculated_level],
(replicate(' ', ctequery1.Calculated_level) + '+ ' + ctequery1.anchor_MenuDesc) as [Calculated_Indented]
from
ctequery as ctequery1
order by
ctequery1.Calculated_SortOrder

13
Resources

Helpful Webpages Found:

14
Helpful Webpage Links found

CTE Query in LINQ: https://linq2db.github.io/articles/sql/CTE.html

SQL CTE: https://www.tutorialgateway.org/sql-server-cte/

Recursive: https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b

15
Sample Build

Finally, the PowerPoint ends, and we get to see

this in action! Right?

Wrong
16
Step 1

Create ANCHOR TOP query:

• MENU Table
• Fields:
• MenuID
• MenuDesc
• Sequence
• SortOrder (Calculated = cast(menuID as nvarchar(500)
• Level (calculated) = 0

Test
Turn into CTE Query

17
Step 2 – Query 2

Create UNIONALL query (as a TOP)

• MENU Table
• Link to CTE Anchor query
• Fields:
• MenuID
• MenuDesc
• Sequence
• SortOrder (Calculated =
• cast(AnchorSortOrder + ‘, ‘
+ right(‘0000’
+ cast (child sequence as nvarchar(4)),4) as nvarchar(500))
• Level (calculated) = Anchor.Level + 1

Test
Turn into UNIONALL Query

18
Step 3 – Query 3

Create TOP query (as a TOP)

• CTE Anchor query
• Fields: (include ALL fields)

Test
Specify SORT order in query & test again

Add Indented Display

Remove unnecessary fields

OPTIONAL: Add additional tables.

19
DEMO

(Tim, it is time to go to the live demo)

20
 Recursive Screenshots – The SQL we will build
with [ctequery] as (
select
[anchor].[MenuID] as [anchor_MenuID],
[anchor].[MenuDesc] as [anchor_MenuDesc],
(cast (anchor.MenuID as nvarchar(500))) as [Calculated_SortOrder],
(0) as [Calculated_level]
from
Ice.Menu as anchor
where
(anchor.ParentMenuID = '')
union all
select
[childMenus].[MenuID] as [childMenus_MenuID],
[childMenus].[MenuDesc] as [childMenus_MenuDesc],
(cast (ctequery.Calculated_SortOrder + ', ' + right('0000' + cast(childMenus.Sequence as varchar(4)),4) as nvarchar(500))) as [Calculated_SortOrder],
(ctequery.Calculated_level + 1) as [Calculated_level]
from
ctequery as ctequery
inner join Ice.Menu as childMenus on childMenus.ParentMenuID = ctequery.anchor_MenuID)

select
[ctequery1].[anchor_MenuID] as [anchor_MenuID],
[ctequery1].[anchor_MenuDesc] as [anchor_MenuDesc],
[ctequery1].[Calculated_SortOrder] as [Calculated_SortOrder],
[ctequery1].[Calculated_level] as [Calculated_level],
(replicate(' ', ctequery1.Calculated_level) + '+ ' + ctequery1.anchor_MenuDesc) as [Calculated_Indented]
from
ctequery as ctequery1
order by
ctequery1.Calculated_SortOrder

21
Questions?

Your Turn to talk

22
Thank you
Tim Shoemaker
Principal Consultant

tshoemaker@epicor.com
+1.949.294.0203

23