Query Formula Causing "Bad SQL Statement"

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
	
	```
1 Like
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]
1 Like

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

1 Like

How do i look up the event log on baqs?

It is on the server side, under Event viewer
image
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)

2 Likes

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.