So I’m trying to make a simple BAQ. I have a part (a pulley) that always requires a bushing to go with it. There are some incorrect BOM’s where the bushing isn’t called out. I want to see where a parent assembly called for one part, but not the other. I should be able to do a full outer join with with the same table like shown.
However the SQL being generated is doing a where clause on the results, so the outer join isn’t working right. Does anyone know what I am doing wrong or a workaround to make this work?
select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum]
from Erp.PartMtl as PartMtl
full outer join Erp.PartMtl as PartMtl1 on
PartMtl.Company = PartMtl1.Company
and PartMtl.PartNum = PartMtl1.PartNum
and ( PartMtl1.MtlPartNum = '603-0-0050' )
where (PartMtl.MtlPartNum = '604-0-0035')
So it looks I can do a left join, (or a right join if I change the order of the tables) but a full join will not get results both tables unless the rows match. The first table in the list will limit all of the results to existing in that table, and it will show when the second table doesn’t exist, but I can’t get it to go both ways with the full join.
Is this a known limitation? With SQL or the BAQ? or is this something that is a bug that I should report.
If you’re doing this as a one-off, you could include a filed from the 2nd table in the displayed columns. Then look for records with that field being blank
Here’s the output of BOMs that contain PN1. BOM’s w/o PN2 show as the ones with blanks ParentPart
Edit: if you make the above a Sub-Query, it could then be filtered on records where ParentPart ISNULL
It’s 3 subs
Sub2 (inner) - finds BOM’s with both Parts
Sub1 (inner) - finds BOMs With Part A, if BOM doesn’t contain Part B, that field will be null
Sub0 (top) - filters Sub1 to show only when Part B is null
What you have would basically be a left join, which I can do with one level. (from your description, I haven’t opened up the BAQ yet)
I got the information I needed for this specific problem (hence the excel screen shot), But I’m worried about the full join not doing what a fill join should be doing for future BAQ’s.
yeah. They are dependent parts. You can’t have one without the other. So just want to see where there is one without the other.
A full join, with criteria should do that, as far as I know. But hopefully someone with more SQL experience can tell me what I should do instead.
There are other areas where I want to check for matching things and show me where they don’t match (both ways) and I’m worried that it won’t work the way I expect.
Yeah, so basically, you have to avoid putting table criteria on anything with an outer join, or it will give you problems. If you put the table into a subquery and filter it there first, it will work.
So that’s the workaround.
Does anyone agree that this isn’t how it should work? Or am I just being overly critical?
May I suggest another method that includes no joins?
Create two calculated fields: NumPartA and NumPartB. These fields are numeric and are equal to 1 if the PartMtl is equal to the selected part (A or B parameters) and 0 (zero) if not. Now, subtotal these by Part and Rev.
In the subtotals, if both totals are zero, then you don’t care. If the totals don’t match then you have one part but not another. No links. One run. No errors.
No post-processing. Use SubQuery Criteria to check if both calculated fields are non-zero. Remember, there are TWO totals: PartA and PartB, so we’re interested whenever there’s a difference. 2 and 0 is just as wrong as 1 and 0, or 2 and 1.
“Create two calculated fields: NumPartA and NumPartB. These fields are numeric and are equal to 1 if the PartMtl is equal to the selected part (A or B parameters) and 0 (zero) if not. Now, subtotal these by Part and Rev.”
How am I subtotaling these two values? like:
SUM(case when PartMtl.MtlPartNum = @PartA then 1 else 0 end)
And then grouping by PartNum and Rev like:
Or do I need two more calc fields to sum up NumPartA and NumPartB?
Edit: Adding subquery criteria based on NumPartA and NumPartB generate error:
“An Aggregate may not appear in the WHERE clause …”
- to filter out the zeros, no?
