I Still Don't Understand CTEs

,

Good afternoon,
I am setting up a BAQ to show the calculated global scheduling sequence for our jobs. I would like to see the jobs and the children assemblies together. If a job has multiple assemblies, then it should list the top level, then the child assembly. I don’t think my current BAQ is doing that. I have been messing around with the linking trying to figure out the right fields to use.

Even after setting up the CTE following the epicor example, I still don’t quite understand CTEs in general. Can anyone point me in the right direction?
GlobalSchedulingOrder-CTE.baq (52.0 KB)

Thanks!
Nate

2 Likes

Yes they are difficult to understand. I recommend playing around with them in SQL to learn how they work. Then less of an abstract mental leap to get them working in Epicor.
This page helped me:

I managed to make this BAQ (starting with zero CTE knowlege) in a couple days. It compares a job bom to the method tracker bom.
image

Unfortunately I don’t have 2022.1.11 installed and can’t review your BAQ.

3 Likes

@NateS CTEs are used for recursion, but that’s not all they can do. Don’t get caught up in learning two topics at the same time.

They way I look at it is a CTE gives me more flexibility to ‘join’ a data set without it being bound to the core FROM clause. Think summary data at the CTE joined to a regular query. Some times you just cant get everything to line up in a single query and you don’t want to build a view in the DB, so use a CTE like a View, and get what you need all in the same query. The BAQ editor calls it a Subquery of type CTE, and that’s about the gist of it. Plus you should know that it evaluates it first, before the rest of the query

Now - recursion and CTE’s. That takes a bit more understanding of how to construct the SQL statement and how the execution plan will retrieve data each time the CTE is referenced. That’s another day…

6 Likes

Great distinction Mike!

1 Like

The way I like to think about CTEs is as tables you can build yourself in memory. If you can figure out a way to build it, you can do it with a CTE. Also, you can build multiple CTEs in one overall query and reference prior CTEs in subsequent ones. I never learned SQL in a proper setting, so I find CTEs to be the greatest things ever since I never learned subqueries. A CTE gives me a table I can structure myself and know what is in it so I can use it later. Here is an example of a CTE that I built once. I am sure there is probably a “better” way to do this, but since I never took any SQL classes I hack my way through with CTEs.

with CTE1(StartFP,StartFY,EndFP,EndFY)
as
(
	select (case when FiscalPeriod - 6 < 1 then (12 + (FiscalPeriod - 6))
				else FiscalPeriod - 6
				end )as StartFP,
				(case when FiscalPeriod - 6 < 1 then FiscalYear -1
				else FiscalYear end) as StartFY,
				FiscalPeriod as EndFP, FiscalYear as EndFY
	from dbo.FiscalPer
	where FiscalCalendarID = 'XXX' and StartDate <= GETDATE() and EndDate >= GETDATE()
),
CTE2 (StartDay)
as
(
	select StartDate
	from dbo.FiscalPer
	where FiscalYear = (select StartFY from CTE1) and FiscalPeriod = (select StartFP from CTE1)
),
CTE3 (EndDay)
as
(
	select EndDate
	from dbo.FiscalPer
	where FiscalYear = (select EndFY from CTE1) and FiscalPeriod = (select EndFP from CTE1)
),
CTE([Day],EndDay)
as
(
	select StartDate as [Day], (select EndDay from CTE3) as EndDay
	from dbo.FiscalPer
	where FiscalCalendarID = 'XXX' and FiscalYear = (select StartFY from CTE1) and FiscalPeriod = (select StartFP from CTE1)
	union all
	select dateadd(d,1,[Day]), EndDay
	from CTE
	where [Day] +1 <= EndDay
),
CTE4 ([Day],FP,FY,[$ Sales], [$ GM], [$ Bookings],[$GM])
as
(
	select CTE.[Day], FiscalPeriod as FP, FiscalYear as FY, 0 as [$ Sales], 0 as [$ GM], 0 as [$ Bookings], 0 as [$GM]
	from CTE
	left join FiscalPer as f
	on CTE.[Day] >= f.StartDate and CTE.[Day] <= f.EndDate
	where f.FiscalCalendarID = 'XXX'
),
CTE5 ([Day],FP,FY,[$ Sales], [$ GM], [$ Bookings],[$GM])
as
(
	select CTE4.[Day], FiscalPeriod as FP, FiscalYear as FY, 0 as [$ Sales], 0 as [$ GM], 0 as [$ Bookings], 0 as [$GM]
	from CTE4
	left join FiscalPer as f
	on CTE4.[Day] >= f.StartDate and CTE4.[Day] <= f.EndDate
	where f.FiscalCalendarID = 'XXX'
	union
	select ih.InvoiceDate as [Day], ih.FiscalPeriod as FP, ih.FiscalYear as FY, SUM(id.ExtPrice) as [$ Sales],
	SUM(id.ExtPrice) - (SUM((id.MtlUnitCost + id.LbrUnitCost + id.BurUnitCost + id.SubUnitCost + id.MtlBurUnitCost) * id.OurShipQty)) as [$ GM],
	 0 as [$ Bookings], 0 as [$GM]
	from CTE4
	join dbo.InvcHead as ih
	on CTE4.[Day] = ih.InvoiceDate
	join dbo.InvcDtl as id
	on ih.Company = id.Company and ih.InvoiceNum = id.InvoiceNum
	where ih.Company = 'XXX'
	group by ih.InvoiceDate, ih.FiscalPeriod, ih.FiscalYear
	union
	select b.BookDate as [Day], CTE4.FP as FP, CTE4.FY as FY, 0 as [$ Sales], 0 as [$ GM], SUM(b.BookValue) as [$ Bookings], 
	SUM(b.BookValue) - SUM(b.SellingBookQty * (p.StdBurdenCost + p.StdLaborCost + p.StdMaterialCost + p.StdMtlBurCost + p.StdSubContCost))
	from CTE4
	join dbo.BookDtl as b
	on CTE4.[Day] = b.BookDate
	join dbo.OrderDtl as o
	on b.Company = o.Company and b.OrderNum = o.OrderNum and b.OrderLine = o.OrderLine
	join dbo.PartCost as p
	on o.Company = p.Company and o.PartNum = p.PartNum
	where b.Company = 'XXX'
	group by b.BookDate, CTE4.FP, CTE4.FY
),
CTE6 ([Day],FP,FY,[$ Sales], [$ GM], [$ Bookings],[$GM])
as
(
	select [Day], FP, FY, MAX([$ Sales]) as [$ Sales], MAX([$ GM]) as [$ GM], MAX([$ Bookings]) as [$ Bookings], MAX([$GM]) as [$GM]
	from CTE5
	group by [Day], FP, FY
)
select [Day], FP, FY, [$ Sales], [$ GM], (case when [$ GM] = 0 then 0 else [$ GM]/[$ Sales] end) as [%],
	[$ Bookings], [$GM], (case when [$GM] = 0 then 0 else [$GM]/[$ Bookings] end) as [% ]
from CTE6
option (maxrecursion 500);
4 Likes

I can’t write my queries in SQL. I just have the BAQ editor to work with.

2 Likes

I just added that as an example of what you can do with CTEs. While this one is in SQL, I could replicate it in Epicor.

2 Likes

So I want to join jobs with their related subassemblies. For example, if ASM 0 has three subs: 1, 2, and 3. Sub 3 is a child of sub 1. I want to be able to see the schedule sequence for ASM0, ASM1, ASM3, then ASM2. If there are more levels of children, I want them to flow down in order. With the example above, if ASM 3 has the subs 4, 5, 6, then I want to see: ASM0, ASM1, ASM3, ASM4, ASM5, ASM6, ASM2.

I can see the child and parent fields in JobAsmbl. But I just cant seem to wrap my head around this kind of recursion.

EDIT: I may be confused again here. I see one large job that has lots of subs. The schedule sequence for this job is always the same regardless of assembly level. I guess I was thinking that the sub assemblies were their own jobs just linked to the top level job. It seems that is not always true.

To do what you want, you will need to use the Parent, PriorPeer, NextPeer, Child fields in JobAsmbl table.

The way that I think about the recursive queries is the following… I used the MENU structure to get my head around it.

  1. First you create an “anchor” query (I actually named it this for my learning). It is the CTE query. To REALLY get your head around this, only select one record (ONE Specific TOP Menu option)
  2. Second you create a UNION ALL query. This is the part that does the recursion, and I called it “Recursion”. Note that all the columns in this query MUST match the previous Anchor query. IN this query, you link your Anchor query to the next series of data… in the menu example, I linked the PARENT menu to the previous CTE Menu that was retrieved.
  3. THird you need the TOP query. This simply displays the data Recursion query. but you can also join the recursion data to other tables if you like.
7 Likes

@jkane - I agree with this wholeheartedly. CTE’s are so very convenient when you just want the data to look like you want it to look. And since it’s “in memory”, there’s no lasting footprint or SYADMIN to get in your way. Having said that - you can’t just bring in LaborDtl as a CTE and not expect some performance problems, so your CTE’s should have the same performance/structure scrutiny that your other queries do as well.

2 Likes