I am trying to add the two fields above. In some cases the WIPQty is what I am assuming is “blank”. If the field is blank I would like the BAQ to treat it as a “0” and add for example 99 + 0 = 99.
What do I need to do to the blank field in order for the calculation to treat it as a “0”?
Once again, thank you so much for your help!
In the calculated field, test the WIP-qty first like
(case when Is Null(WIPqty) then 0 else WIPqty end) + OnHandQty
That’s off the top of my head. Use the helpers in the BAQ Calculated field editor.
A bit shorter but maybe harder to read if you aren’t used to it:
isnull(WIPQty, 0) + OnHandQty
IsNull() will return the first value unless it is null, then it will return the second value.
I understand what both of you are saying and I have tried both solutions, but I’m not sure I explained myself sufficiently.
Right now I have a Subquery that sums the WIPQty and a Subquery that sums the OnHand Qty and a third Subquery that adds the two together.
I’ve tried adding the code mentioned in both your posts at the Subquery level of WIPQty and when that didn’t seem to work I added the code to the 3rd subquery where I am adding the two together. Neither seemed to work.
Perhaps one of you can tell me what I’m missing?
Thank you for your patience!
I don’t know if the code here will help you understand what I’m doing - but here it is. Unfortunately I am only familiar with BAQ Designer and not so much the code.
select
[Part].[PartNum] as [Part_PartNum],
[Part].[NonStock] as [Part_NonStock],
[Part].[ProdCode] as [Part_ProdCode],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
[PartPlant].[MinMfgLotSize] as [PartPlant_MinMfgLotSize],
[SubQuery3].[Calculated_OnHand] as [Calculated_OnHand],
[SubQuery2].[Calculated_WipQty] as [Calculated_WipQty],
[SubQuery5].[Calculated_Total] as [Calculated_Total]
from Erp.Part as Part
left outer join (select
[Part1].[PartNum] as [Part1_PartNum],
[JobProd].[WIPQty] as [JobProd_WIPQty],
(sum( JobProd.WIPQty )) as [Calculated_WipQty]
from Erp.Part as Part1
inner join Erp.JobHead as JobHead on
Part1.Company = JobHead.Company
and Part1.PartNum = JobHead.PartNum
and ( JobHead.JobClosed <> TRUE and JobHead.JobReleased = Yes )
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
where (Part1.InActive = NO)
group by [Part1].[PartNum],
[JobProd].[WIPQty]) as SubQuery2 on
SubQuery2.Part1_PartNum = Part.PartNum
left outer join (select
[Part2].[PartNum] as [Part2_PartNum],
(sum( PartBin.OnhandQty )) as [Calculated_OnHand]
from Erp.Part as Part2
inner join Erp.PartBin as PartBin on
Part2.Company = PartBin.Company
and Part2.PartNum = PartBin.PartNum
and ( not PartBin.BinNum like '%MRB' )
where (Part2.InActive = NO)
group by [Part2].[PartNum]) as SubQuery3 on
SubQuery3.Part2_PartNum = Part.PartNum
inner join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
and Part.PartNum = PartPlant.PartNum
left outer join Erp.PartDtl as PartDtl on
Part.Company = PartDtl.Company
and Part.PartNum = PartDtl.PartNum
left outer join Erp.Customer as Customer on
Customer.Company = PartDtl.Company
and Customer.CustNum = PartDtl.CustNum
left outer join (select
[Part3].[PartNum] as [Part3_PartNum],
(Isnull(SubQuery21.Calculated_WipQty,0) + (SubQuery31.Calculated_OnHand)) as [Calculated_Total],
[SubQuery21].[Calculated_WipQty] as [Calculated_WipQty],
[SubQuery31].[Calculated_OnHand] as [Calculated_OnHand]
from Erp.Part as Part3
inner join (select
[Part1].[PartNum] as [Part1_PartNum],
[JobProd].[WIPQty] as [JobProd_WIPQty],
(sum( JobProd.WIPQty )) as [Calculated_WipQty]
from Erp.Part as Part1
inner join Erp.JobHead as JobHead on
Part1.Company = JobHead.Company
and Part1.PartNum = JobHead.PartNum
and ( JobHead.JobClosed <> TRUE and JobHead.JobReleased = Yes )
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
where (Part1.InActive = NO)
group by [Part1].[PartNum],
[JobProd].[WIPQty]) as SubQuery21 on
SubQuery21.Part1_PartNum = Part3.PartNum
inner join (select
[Part2].[PartNum] as [Part2_PartNum],
(sum( PartBin.OnhandQty )) as [Calculated_OnHand]
from Erp.Part as Part2
inner join Erp.PartBin as PartBin on
Part2.Company = PartBin.Company
and Part2.PartNum = PartBin.PartNum
and ( not PartBin.BinNum like '%MRB' )
where (Part2.InActive = NO)
group by [Part2].[PartNum]) as SubQuery31 on
SubQuery31.Part2_PartNum = Part3.PartNum
where (Part3.InActive = NO)
group by [Part3].[PartNum],
[SubQuery21].[Calculated_WipQty],
[SubQuery31].[Calculated_OnHand]) as SubQuery5 on
SubQuery5.Part3_PartNum = Part.PartNum
where (Part.InActive = NO and Part.ProdCode like '%PAR' and Part.NonStock <> TRUE)
group by [Part].[PartNum],
[Part].[NonStock],
[Part].[ProdCode],
[PartPlant].[MinimumQty],
[PartPlant].[MinMfgLotSize],
[SubQuery3].[Calculated_OnHand],
[SubQuery2].[Calculated_WipQty],
[SubQuery5].[Calculated_Total]
When you say “Doesn’t work” what exactly do you mean? Does it return the wrong answer? Or throw an error?
Actually I just got it to work as far as at least giving me a total with or without an onhand qty or wipqty by using the joins correctly!
**OH Qty WIP Qty Total**
10 10 20
10 10
10 10
I was expecting the previous suggestions to return a ‘0’ rather than null.
**OH Qty WIP Qty Total**
10 10 20
10 0 10
0 10 10
I have one part that is not “summing” correctly - not sure what that is all about but I’m digging in.
Thank you for looking at my issue - I appreciate the gesture!
In the lower subs that total QOH and WIPQty, make the calculated field that sums, to be like
isnull(SUM(whatever_field_you_are_summing_to_get_the_total), 0)
Then that sub will return a zero even when no records found.
Then in your “top” query, you can just reference that sub’s calculated field. No need to check for null, asit will always have a value.
Perfect - Thank you so much it all makes sense now!
You’re awesome!!!