So i have this query:
I have this calculated field:
**You can see in the first image the query calculates just fine. **
But I modified the calculation to something else (Syntax checks out okay):
And now look what I get when i run the query:
I dont understand why it is doing this. If i was to do this calculation in Excel is works fine.
These are all my other calculations:
From Top level Query
From SubQuery:
Can you post the actual SQL query statement from the General tab?
(Don’t forget to put it inside
``` SQL
```
select
[ProdGrup].[Description] as [ProdGrup_Description],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
(sum( PartTran.TranQty )) as [Calculated_ReceivedToStock],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobMtlsub].[Calculated_TotalIssued] as [Calculated_TotalIssued],
[JobMtlsub].[JobMtl_QtyPer] as [JobMtl_QtyPer],
(ReceivedToStock*JobMtlsub.JobMtl_QtyPer) as [Calculated_TotalComplete],
(JobMtlsub.Calculated_TotalIssued-TotalComplete) as [Calculated_Diff],
(Diff/JobMtlsub.Calculated_TotalIssued*100) as [Calculated_ScrapPerc],
[JobHead].[ClosedDate] as [JobHead_ClosedDate]
from Erp.JobHead as JobHead
inner join Erp.PartTran as PartTran on
JobHead.Company = PartTran.Company
and JobHead.JobNum = PartTran.JobNum
and ( PartTran.TranType in ('MFG-CUS', 'MFG-STK') )
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join (select
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
[JobMtl].[QtyPer] as [JobMtl_QtyPer],
[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
(sum( JobMtl.IssuedQty )) as [Calculated_TotalIssued]
from Erp.JobMtl as JobMtl
group by [JobMtl].[JobNum],
[JobMtl].[AssemblySeq],
[JobMtl].[MtlSeq],
[JobMtl].[QtyPer],
[JobMtl].[RequiredQty]) as JobMtlsub on
JobAsmbl.JobNum = JobMtlsub.JobMtl_JobNum
and JobAsmbl.AssemblySeq = JobMtlsub.JobMtl_AssemblySeq
inner join Erp.ProdGrup as ProdGrup on
JobHead.Company = ProdGrup.Company
and JobHead.ProdCode = ProdGrup.ProdCode
and ( ProdGrup.ProdCode = @Group )
where (JobHead.JobNum like '18%' and JobHead.ClosedDate >= @StartDate and JobHead.ClosedDate <= @EndDate)
group by [ProdGrup].[Description],
[JobHead].[JobNum],
[JobHead].[PartNum],
[JobHead].[ProdQty],
[JobAsmbl].[PartNum],
[JobAsmbl].[AssemblySeq],
[JobAsmbl].[RequiredQty],
[JobMtlsub].[Calculated_TotalIssued],
[JobMtlsub].[JobMtl_QtyPer],
[JobHead].[ClosedDate]
Is it possible that Calculated_TotalIssued be zero? Then your error would be divide by zero…
What does it say in the Event log for the error details?
Pierre
How do i look up the event log on baqs?
It is on the server side, under Event viewer
I was getting the same error…due to a datatype conversion error… but I noticed it always say BAD SQL Statement whatever the error is…
Maybe try to view the result of the calculated field before using it in your formula…you can then visualize if all values are as expected…
Pierre
I dont have that access, I am a SaaS environment.
I dont undertstand “viewing the results of the calculated field before using in formula”
I second that. You need a case statement to handle 0’s.
One way to check for zero:
(case when JobAsmbl.RequiredQty > 0 then Diff/JobAsmbl.RequiredQty*100 else 0 end)
I’d lean toward the div by zero error. Especially since some rows are returned prior to throwing an error.
To “visualize it” change the formula to be
Diff / (JobAsy.QtyRequired + 0.001) * 100
Or better yet, do the check for QtyRequired being zero.
I think that did it.