SQL join statement issues

Epicor ERP 10
, , ,
1

I’ve asked a very similar question here before, so I apologize for being so slow.

I’m still having issues with existing queries producing unexpected results, and I know it’s just because I don’t fully understand join statements yet. This query returns exactly 1 row:

SELECT *
FROM Erp.JobHead
WHERE JobHead.JobNum = '006500'

Whereas this returns 10:

select  *
from Erp.OrderHed as OrderHed

inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.OpenLine = 1  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = 1  )

inner join Erp.Part as Part on 
	OrderRel.Company = Part.Company
	and OrderRel.PartNum = Part.PartNum

inner join Erp.JobHead as JobHead on 
	OrderHed.Company = JobHead.Company
	and Part.PartNum = JobHead.PartNum
	and ( JobHead.JobNum = '006500'  )

inner join Erp.PartPlant as PartPlant on 
	OrderHed.Company = PartPlant.Company
	and Part.PartNum = PartPlant.PartNum

inner join Erp.PartWhse as PartWhse on 
	OrderHed.Company = PartWhse.Company
	and Part.PartNum = PartWhse.PartNum
	
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum

where (OrderHed.OpenOrder = 1)
order by JobHead.JobNum

What am I missing? Am I just lacking criteria? Experiments with the join type have not yielded anything valuable.

2

What is being returned in the 10 results, is it duplicate data?

3

…Sort of? I pulled this out of the BAQ and into SSMS and just did a Select * hoping I could answer that question, but now I’m only more confused. There’s some variance on some fields, but nothing that stands out as being unique for each row. I could upload the results somewhere if it helps.

4

Change select * to select JobHead.JobNum, JobHead.PartNum, OrderRel.PartNum, OrderRel.OrderNum, OrderRel.OrderLine, *
you will see that you select all OrderHed with specified Part assigned to JobNum = ‘006500’.
Try to change table order in the query.

5

I think the issue is with how you’re joining order details to job details.
If the intention is to get results for that one job, 006500, then I would do something more like:

select  * 
from Erp.JobHead as JobHead
inner join Erp.JobProd as JobProd on 
	JobHead.Company = JobProd.Company
	and JobHead.JobNum = JobProd.JobNum
inner join Erp.OrderDtl as OrderDtl on 
	JobProd.Company = OrderDtl.Company
	and JobProd.OrderNum = OrderDtl.OrderNum
	and JobProd.OrderLine = OrderDtl.OrderLine
where (JobHead.JobNum = '006500')

Then add the other tables and display fields from there.

In the BAQ designer, when you add a table, it will automatically join a linked table for you so you don’t have to define the join clause.

1 Like
6

The statement’s only real limitations here are that the part number be the same as produced on job 006500, the company be the same throughout, and the order/line/release be open. I expect the data should show a new row for every open release at that company for that part number.

It’s exponentially adding rows whenever it encounters varying data on a joined table. Ie. if the part can exist in multiple plants and/or warehouses since those are not being limited here. Both whse West + order/line/release 1 and whse East + order/line/release 1 are two different data sets. (Similar to your last post on this topic). So, I’ll hazard 5 open releases but the part can be housed in 2 different warehouses nets the 10 results. Unlocking the job number presents a 3rd unique data set so that’s 10 results per job for that part. We haven’t limited to only open jobs yet either.

I’d also suggest getting the JobProd table joined in here to trim your results since that’s where demand lives. If the job was make to order and this is a “where am I supplying these parts to?” detail report, then JobProd will provide a 1 to 1 link between the job and releases as shown in jnbadger’s reply above.

However, since the original query is pulling warehouse data into it, I suspect this is more likely a “where can I supply these parts to?” report for a make to stock job. Joining JobProd on JobHead.JobNum = JobProd.JobNum and further joining PartWhse on PartWhse.Warehousecode = JobProd.Warehousecode will at least remove any extra plants or warehouses that job 006500 isn’t feeding. Unless the job has multiple make to stock demand links and we’re just swapping which table is doing the multiplying.

1 Like
7

Thanks all. You guys are awesome.

1 Like