Calculating with null fields

Epicor ERP 10
1

I am trying to do a calculation that may sometimes have a null field… when null i want to replace with 0 but still not getting the value that i want. I have tried the following and it’s not working. Any suggestions?

case when PartTran2.Company <> null then (sum(PartTran2.TranQty * PartTran2.LbrUnitCost)) else 0 end

case when PartTran2.Company = null then 0 else (sum(PartTran2.TranQty * PartTran2.LbrUnitCost)) end

sum( case when PartTran2.Company = null then 0 else (PartTran2.TranQty * PartTran2.LbrUnitCost) end

sum( case when PartTran2.Company <> null then (PartTran2.TranQty * PartTran2.LbrUnitCost) else 0 end

2

Maybe use DBNull instead of null?

3

You’d do “is null” instead of “<> null”
Or you can just do isnull(sum(PartTran2.TranQty * PartTran2.LbrUnitCost). 0)

4

Still no results. I’m stumped

5

How about.

sum(isnull(PartTran2.TranQty,0) * isnull(PartTran2.LbrUnitCost,0))

7

I just tried to use this calculation and still no results.

8

These columns would never be null, if no value then it’s zero.

9

This is for trantype MFG-VAR

10

Still never going to be null. What is the end goal of the query?

11

Can you please post the whole query? That would help.

13

You have a few moving parts here… (lol)…

Which fields are you not wanting null.

With the left joins you will have a null values for the non calculated fields in the main. For example these fields could be null. Then with your group by be causing the issue you are seeing.

[JobAsmbl1].[Calculated_ToDateLabor] as [Calculated_ToDateLabor],
[JobAsmbl1].[Calculated_ToDateBurden] as [Calculated_ToDateBurden],
[JobAsmbl1].[Calculated_ToDateMaterial] as [Calculated_ToDateMaterial],
[JobAsmbl1].[Calculated_ToDateSub] as [Calculated_ToDateSub],

Then you have the following fields being calculated. One of these could be null.

(sum(case when Varience_.PartTran2_Company is null then JobAsmbl1.Calculated_ToDateLabor-PartTran1.Calculated_ToInvLabor else JobAsmbl1.Calculated_ToDateLabor-PartTran1.Calculated_ToInvLabor-Varience_.Calculated_MfgVarLabor end)) as [Calculated_WIPLabor],
(sum( case when Varience_.PartTran2_Company is null then JobAsmbl1.Calculated_ToDateBurden-PartTran1.Calculated_ToInvBurden else JobAsmbl1.Calculated_ToDateBurden-PartTran1.Calculated_ToInvBurden-Varience_.Calculated_MfgVarBurden end)) as [Calculated_WIPBurden],
(sum(case when Varience_.PartTran2_Company is null then JobAsmbl1.Calculated_ToDateMaterial-PartTran1.Calculated_ToInvMaterial else JobAsmbl1.Calculated_ToDateMaterial-PartTran1.Calculated_ToInvMaterial-Varience_.Calculated_MfgVarMaterial end)) as [Calculated_WIPMaterial],
(sum(case when Varience_.PartTran2_Company is null then JobAsmbl1.Calculated_ToDateSub-PartTran1.Calculated_ToInvSub else JobAsmbl1.Calculated_ToDateSub-PartTran1.Calculated_ToInvSub-Varience_.Calculated_MfgVarSub end)) as [Calculated_WIPSub],
(sum(case when Varience_.PartTran2_Company is null then JobAsmbl1.Calculated_ToDateMtlBurden-PartTran1.Calculated_ToInvMtlBurden else JobAsmbl1.Calculated_ToDateMtlBurden-PartTran1.Calculated_ToInvMtlBurden-Varience_.Calculated_MfgVarMtlBurden end)) as [Calculated_WIPMtlBurden]-

14

The only fields that are coming up null are the fields under the Varience subquery when transaction type MFG-VAR is not found for a given job. For those I want to populate a 0 because later that sum will be used in another calculation.

15

Let rebuild the displayed fields for the main then. I would add one calculated field at a time until you get all 6 of them.

Get to this point then we can add back the other fields, I don’t think you want them apart of the group by.

select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[JobHead].[RevisionNum] as [JobHead_RevisionNum],
[JobHead].[IUM] as [JobHead_IUM],
(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateLabor,0)-isnull(PartTran1.Calculated_ToInvLabor,0) else isnull(JobAsmbl1.Calculated_ToDateLabor,0)-isnull(PartTran1.Calculated_ToInvLabor,0)-isnull(Varience_.Calculated_MfgVarLabor,0) end)) as [Calculated_WIPLabor],
(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateBurden,0)-isnull(PartTran1.Calculated_ToInvBurden,0) else isnull(JobAsmbl1.Calculated_ToDateBurden,0)-isnull(PartTran1.Calculated_ToInvBurden,0)-isnull(Varience_.Calculated_MfgVarBurden,0) end)) as [Calculated_WIPBurden],
(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateMaterial,0)-isnull(PartTran1.Calculated_ToInvMaterial,0) else isnull(JobAsmbl1.Calculated_ToDateMaterial,0)-isnull(PartTran1.Calculated_ToInvMaterial,0)-isnull(Varience_.Calculated_MfgVarMaterial,0) end)) as [Calculated_WIPMaterial],
(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateSub,0)-isnull(PartTran1.Calculated_ToInvSub,0) else isnull(JobAsmbl1.Calculated_ToDateSub,0)-isnull(PartTran1.Calculated_ToInvSub,0)-isnull(Varience_.Calculated_MfgVarSub,0) end)) as [Calculated_WIPSub],
(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateMtlBurden,0)-isnull(PartTran1.Calculated_ToInvMtlBurden,0) else isnull(JobAsmbl1.Calculated_ToDateMtlBurden,0)-isnull(PartTran1.Calculated_ToInvMtlBurden,0)-isnull(Varience_.Calculated_MfgVarMtlBurden,0) end)) as [Calculated_WIPMtlBurden]–,
–(sum(JobAsmbl1.Calculated_ToDateTotal-PartTran1.Calculated_ToInvTotal-Varience_.Calculated_MfgVarTotal)) as [Calculated_WIPTotal]
from Erp.JobHead as JobHead
left outer join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
And
JobHead.JobNum = JobProd.JobNum

left outer join (select
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Company] as [JobAsmbl_Company],
(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLALaborCost + JobAsmbl.LLALaborCost) else 0 end)) as [Calculated_ToDateLabor],
(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLABurdenCost + JobAsmbl.LLABurdenCost) else 0 end)) as [Calculated_ToDateBurden],
(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLAMaterialCost + JobAsmbl.LLAMaterialCost) else 0 end)) as [Calculated_ToDateMaterial],
(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLASubcontractCost + JobAsmbl.LLASubcontractCost) else 0 end)) as [Calculated_ToDateSub],
(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLAMtlBurCost + JobAsmbl.LLAMtlBurCost) else 0 end)) as [Calculated_ToDateMtlBurden],
(ToDateLabor+ToDateBurden+ToDateMaterial+ToDateSub+ToDateMtlBurden) as [Calculated_ToDateTotal]
from Erp.JobAsmbl as JobAsmbl
group by [JobAsmbl].[JobNum],
[JobAsmbl].[PartNum],
[JobAsmbl].[Company]) as JobAsmbl1 on
JobHead.Company = JobAsmbl1.JobAsmbl_Company
And
JobHead.JobNum = JobAsmbl1.JobAsmbl_JobNum
And
JobHead.PartNum = JobAsmbl1.JobAsmbl_PartNum

left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[JobNum] as [PartTran_JobNum],
[PartTran].[PartNum] as [PartTran_PartNum],
(sum(PartTran.TranQty * PartTran.LbrUnitCost)) as [Calculated_ToInvLabor],
(sum(PartTran.TranQty * PartTran.BurUnitCost)) as [Calculated_ToInvBurden],
(sum(PartTran.TranQty * PartTran.MtlUnitCost)) as [Calculated_ToInvMaterial],
(sum(PartTran.TranQty * PartTran.SubUnitCost)) as [Calculated_ToInvSub],
(sum(PartTran.TranQty * PartTran.MtlBurUnitCost)) as [Calculated_ToInvMtlBurden]–,
(ToInvLabor+ToInvBurden+ToInvMaterial+ToInvSub+ToInvMtlBurden) as [Calculated_ToInvTotal]
from Erp.PartTran as PartTran
where (PartTran.TranType = ‘MFG-STK’)
group by [PartTran].[Company],
[PartTran].[JobNum],
[PartTran].[PartNum]) as PartTran1 on
JobHead.Company = PartTran1.PartTran_Company
And
JobHead.JobNum = PartTran1.PartTran_JobNum
And
JobHead.PartNum = PartTran1.PartTran_PartNum

left outer join (select
[PartTran2].[Company] as [PartTran2_Company],
[PartTran2].[JobNum] as [PartTran2_JobNum],
[PartTran2].[PartNum] as [PartTran2_PartNum],
(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.LbrUnitCost,0) )) as [Calculated_MfgVarLabor],
(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.BurUnitCost ,0) )) as [Calculated_MfgVarBurden],
(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.MtlUnitCost ,0) )) as [Calculated_MfgVarMaterial],
(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.SubUnitCost ,0) )) as [Calculated_MfgVarSub],
(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.MtlBurUnitCost,0) )) as [Calculated_MfgVarMtlBurden]
,
(MfgVarLabor+MfgVarBurden+MfgVarMaterial+MfgVarSub+MfgVarMtlBurden) as [Calculated_MfgVarTotal]
from Erp.PartTran as PartTran2
where (PartTran2.TranType = ‘MFG-VAR’)
group by [PartTran2].[Company],
[PartTran2].[JobNum],
[PartTran2].[PartNum]) as Varience_ on
JobHead.Company = Varience_.PartTran2_Company
And
JobHead.JobNum = Varience_.PartTran2_JobNum
And
JobHead.PartNum = Varience_.PartTran2_PartNum

where (JobHead.JobNum = ‘MRP070007’)
group by [JobHead].[JobNum],
[JobHead].[PartNum],
[JobHead].[PartDescription],
[JobHead].[RevisionNum],
[JobHead].[IUM]

16

Here is my quick baq. You will need to adjust the fields a bit more, but this should get you started.

I was not getting values for WIPMaterial until I added the ISNULL(field,0) syntax.

test-wip.baq (50.8 KB)

17

@jvalladares It has been a week, were you able to get this to work?

18

Sorry I haven’t been on. Been on a large project. It took a little more that just changing the calculation… So basically I had to sub-query the calculation using “is null” and the pull the same table over and attaching it behind the sub-query making the sub-query ultimately act like a filter for the data that I wanted to pull.