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:
WHERE JobHead.JobNum = '006500'
Whereas this returns 10:
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.
What is being returned in the 10 results, is it duplicate data?
…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.
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.
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:
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.
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.
Thanks all. You guys are awesome.