BAQ Circular Join - Quote, Order, and Job tables

Epicor ERP 10
1

Hi there,

I have been working for a while now on a dashboard to analyze our quoted, planned, and actual costs for orders/jobs that are in our database. The idea behind this is to calculate costs and get revenue information from the Quote, Order, and Job in the same line. The basic structure that I think I need, before adding in the QuoteMtl, JobMtl, QuoteOpr, etc. tables and group is as follows:

image

At the moment, I’m just selected the key fields from each of these tables to test the validity of the structure. You can find the query text at the end of this post.

The issue that I’m having is due to the fact that the QuoteDtl table can join to JobHead if details are gotten from the linked quote and/or OrderDtl if Get Opportunity / Quote is used to create the sales order line. I need both of these joins to be present in the query in order to maintain the link between QuoteDtl-OrderDtl when a quote has been converted into a Sales Order but no Job has been created yet or the Job’s details were not pulled in from the linked quote.

I have tried using only one of the joins but that causes the QuoteDtl records to pull in separately from the JobHead or OrderDtl record, depending on which join I keep and which one I remove.

The order of the tables also clearly matters here. When I have the order as shown in the screenshot above, the QuoteDtl-OrderDtl record does not pull in properly in that the QuoteDtl record comes in alone and the OrderDtl fields are null. When I reverse the order (QuoteDtl → Order Dtl → Order Rel → …) that QuoteDtl-OrderDtl record comes in fine but the JobProd record for standalone Jobs (make to stock) do not pull in.

I appreciate any help you can provide on this, I consider myself decent at writing SQL queries but this one really has me stumped.

Query Text:

select 
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[JobProd].[JobNum] as [JobProd_JobNum],
	[JobProd].[OrderNum] as [JobProd_OrderNum],
	[JobProd].[OrderLine] as [JobProd_OrderLine],
	[JobProd].[OrderRelNum] as [JobProd_OrderRelNum],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[QuoteNum] as [JobHead_QuoteNum],
	[JobHead].[QuoteLine] as [JobHead_QuoteLine],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine]
from Erp.QuoteDtl as QuoteDtl
full outer join Erp.OrderDtl as OrderDtl on 
	OrderDtl.Company = QuoteDtl.Company
	and OrderDtl.QuoteNum = QuoteDtl.QuoteNum
	and OrderDtl.QuoteLine = QuoteDtl.QuoteLine
full outer join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
full outer join Erp.JobProd as JobProd on 
	OrderRel.Company = JobProd.Company
	and OrderRel.OrderNum = JobProd.OrderNum
	and OrderRel.OrderLine = JobProd.OrderLine
	and OrderRel.OrderRelNum = JobProd.OrderRelNum
full outer join Erp.JobHead as JobHead on 
	QuoteDtl.Company = JobHead.Company
	and QuoteDtl.QuoteNum = JobHead.QuoteNum
	and QuoteDtl.QuoteLine = JobHead.QuoteLine
full outer join Erp.JobHead as JobHead
	and 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
2

I do not consider myself good at writing SQL, but I never use circular joins. I always assume if you need a circular join, then you really just need some carefully crafted subqueries. What about creating two subqueries. One returns all the OrderDtl records, and one returns all the JobHead records. Then you merge the two together in the top level query based on a common identifier (like part number?) You may even need a third subquery to pull in the JobProd records.
Good luck!

2 Likes
3

My first thought too.

P.S.
Slow day so I tried the attached test BAQ using subqueries…
one thing I noticed is that there might be some duplication of quote lines to “deal with”
Due to links from quotedtl to both jobhead and indirectly to jobprod.
CircularTest_E10.1.4.baq (46.1 KB)

2 Likes
4

Don’t forget that you can bring in the same table twice. So you might have the first instance of the table on the canvas filtered to “Order type” records, and then a second instance of the table filtered to “Job Instance” records. You can also do this in sub queries and the then union them depending on how you want to present the data.

3 Likes
5

Why full joins are used here instead of inner join? Looks wrong

Note: FULL OUTER JOIN can potentially return very large result-sets!

1 Like
6

Hi Olga,

The reason I have used FULL OUTER JOIN instead of INNER JOIN is because I don’t just need rows that have a record in each of these tables. Some rows that I would also want to be included are:

  • Standalone quotes
  • Make-to-Stock jobs
  • Order lines that do not have jobs
  • Order lines that do not have quotes
    And the list goes on.

It is definitely going to be a large data set (well over 150k rows) but ultimately it will be in a dashboard that has many filters to narrow it down.

In your view, which joins could I change from FULL OUTER to INNER?

7

Hi Brandon,

Those are both interesting ideas that I haven’t tried yet. I think the subquery approach, which other commenters have suggested (thank you, by the way) might get me what I need. I have not used UNION too much yet, but I can definitely see how that would work. I will try that out and let you know how it goes.

Thanks again.

8

I think you will end up with list of all rows from all tables you included in the queries.
You should probably start from learning how to do each of this steps you mention:

  • Standalone quotes
  • Make-to-Stock jobs
  • Order lines that do not have jobs
  • Order lines that do not have quotes
    And the list goes on

in separate query and make sure you get results you want.

2 Likes
9

I can already do all of those and have included them in a variety of different dashboards and reports that I’ve written for other reasons. To your first point, though, I ultimately do want all tables I included in the queries to be in the result set, with consolidation of rows happening when a link is found between one or more of the tables, hence the use of FULL OUTER JOINs.

In addition, the joins are not just between the individual tables that the link is drawn between in the Query Builder. From what I understand, that’s where the order of the tables in the FROM statement comes into play - because every time a new table is joined in, it’s not just joined against the specific table I’m joining to but rather the entire joined dataset up to that point.

10

This looks like it could cause significant problems.
You might consider creating multiple SUB QUERIES that are CTE Queries. These CTE Queries would gather the data you want from each set of data (orderhed, orderdtl being a set)… then create upper queries that start to combine that data. A CTE query will simply extract the data into new memory table (non-database) to be worked by later tables. each of the CTE queries could also selectively filter out data that you dont want to include.

1 Like