My advanced BAQ building skills are pretty awful. Can anyone offer help explaining how to achieve the following in a BAQ.
Display all top level PN where CLASS = FG. For each of those records, display PN from BOM where CLASS = MOLD, and display PN and QTY\PER from BOM where CLASS = RESN
I used a subquery and I can easily get either of my subparts (RESN or MOLD) but getting both is a little trickier.
You’ll have to use a CTE and a union within said CTE.
So write a CTE with a union that returns all parts mtls with a partclass of mold and union that to a separate query that returns all part mtls with a PartClass of RSN
Then join onto the CTE to the “TOP” level Part table there you have it
This should get you started BAQPartThing.baq (32.5 KB)
This is the Query Generated
With [CTE] AS
(select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[Part].[ClassID] as [Part_ClassID]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on
PartMtl.Company = Part.Company
And
PartMtl.MtlPartNum = Part.PartNum
and ( Part.ClassID = 'HDW' )
UNION
select
[PartMtlUnion].[PartNum] as [PartMtlUnion_PartNum],
[PartMtlUnion].[Company] as [PartMtlUnion_Company],
[PartMtlUnion].[MtlPartNum] as [PartMtlUnion_MtlPartNum],
[PartUnoin].[PartDescription] as [PartUnoin_PartDescription],
[PartMtlUnion].[RevisionNum] as [PartMtlUnion_RevisionNum],
[PartMtlUnion].[QtyPer] as [PartMtlUnion_QtyPer],
[PartUnoin].[ClassID] as [PartUnoin_ClassID]
from Erp.Part as PartUnoin
inner join Erp.PartMtl as PartMtlUnion on
PartUnoin.Company = PartMtlUnion.Company
And
PartUnoin.PartNum = PartMtlUnion.MtlPartNum
where (PartUnoin.ClassID = 'PWR'))
select
[CTE].[PartMtl_Company] as [PartMtl_Company],
[CTE].[PartMtl_PartNum] as [PartMtl_PartNum],
[CTE].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[CTE].[Part_PartDescription] as [Part_PartDescription],
[CTE].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[CTE].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[CTE].[Part_ClassID] as [Part_ClassID]
from CTE as CTE
order by CTE.PartMtl_PartNum
Have 3 subqueries in total, TopLevel, CTE, and UNION
CTE and Union both look the same except one filters for Class 1 and the other one filter Class 2
Then the Top Query just uses the CTE/Union and can be joined to Part
and it returns Parts that Material with class HDW and PWR
Very nice. Is there a way to have results from both PartMtls (HDW, PWR) on the same line top level?
Like this
Add a calculated field and use a case statement then group by
Actually thinking about it more that’s more difficult… since you have two different records… Would there only be one of each on the assembly? If so you can use an inner subquery… to do it
My recursive function never returns… Bahahah
public Answer AskJose(String Question)
{
Answer A = null;
while(A != Epiphany)
{
A = AskJose(Question);
}
return A;
}
Yep, only 1 class MOLD and 1 class RESN per top level part
OK. So using an inner subquery works… (if there is only 1) you’ll need 4 inner subqueries if you want to get values for both the MtlPartNUm and the Qty Per, the example I attach only uses 2 subqueries since I am just returning the Mtl for Class1 and the Mtl for Class 2 you’ll have to add more subqueries in the same vein to add more fields.
BAQThing2.baq (35.8 KB)
select
[PartTop].[Company] as [PartTop_Company],
[PartTop].[PartNum] as [PartTop_PartNum],
(((select top (1)
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on
PartMtl.Company = Part.Company
And
PartMtl.MtlPartNum = Part.PartNum
where PartMtl.PartNum = PartTop.PartNum and PartMtl.RevisionNum = PartRev.RevisionNum and Part.ClassID = 'HDW'))) as [Calculated_HWD_Mtl],
(((select top (1)
[PartMtlUnion].[MtlPartNum] as [PartMtlUnion_MtlPartNum]
from Erp.Part as PartUnoin
inner join Erp.PartMtl as PartMtlUnion on
PartUnoin.Company = PartMtlUnion.Company
And
PartUnoin.PartNum = PartMtlUnion.MtlPartNum
where PartMtlUnion.PartNum = PartTop.PartNum and PartMtlUnion.RevisionNum = PartRev.RevisionNum and PartUnoin.ClassID = 'PWR'))) as [Calculated_PWR_Mtl]
from Erp.Part as PartTop
inner join Erp.PartRev as PartRev on
PartTop.Company = PartRev.Company
And
PartTop.PartNum = PartRev.PartNum
where (PartTop.PartNum = 'AMM-100-S')
Have 2 Subqueries InnerHWD and InnerPWR
Which are actually not used on the Top query as a Join
But rather in calculated fields, these inner subqueries can only return 1 field to work this way so you’ll need 4 if you want to get 2 values from the PartMtl table
Calculated field
Results
Excellent, I am going to give it a spin. I really only need 2 fields from 1 of the PartMtls so it seems I’ll need 3 subs. I’ll report back.
This is working great - I see where I was confused before, the sub query in the calculate field can only have one field AND one record. I didn’t realize I could use the subquery criteria to drive that - Awesome.
Last question - How can I not return rows where ALL of my calculated fields are null? I can’t find the calculated fields in the criteria editor.
Add another subquery criteria this time to your Top Query and remove those results tehre.
Doh, in the dropdown for table on subcriteria is CALCULATED TABLE… lol
