# Query Formula Causing "Bad SQL Statement" **URL:** \ **Category:** Epicor ERP 10\ **Tags:** baq\ **Created:** [March 19, 2019, 7:58pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735 "2019-03-19T19:58:06Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![tse.dba](https://www.epiusers.help/user_avatar/www.epiusers.help/tse.dba/32/8690_2.png) [@tse.dba](https://www.epiusers.help/u/tse.dba)\ **Post date:** [March 19, 2019, 7:58pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/1 "2019-03-19T19:58:06Z")
**So i have this query:** ![image](https://epiusers.help/uploads/default/original/2X/a/a0974cee17fbf8e0d6c3bac32a4229b5754c0f25.png) ![image](https://epiusers.help/uploads/default/original/2X/7/7e52e22bbead279c7928c807c1d1a8114ec7eb57.png) ![image](https://epiusers.help/uploads/default/original/2X/7/7375eb3ed220fdcae7fa39d8a28ff44b271846fe.png) ![image](https://epiusers.help/uploads/default/original/2X/d/d699d62654feb4f3e4d05d1a9a625f8868d986e9.png) ![image](https://epiusers.help/uploads/default/original/2X/1/18ac2f2c9fc3e133a40b86a59280c5197c4719f5.png) **I have this calculated field:** ![image](https://epiusers.help/uploads/default/original/2X/a/acfff8dda86d2c9dbae62a9b7add446a4428566a.png) \*\*You can see in the first image the query calculates just fine. \*\* **But I modified the calculation to something else (Syntax checks out okay):** ![image](https://epiusers.help/uploads/default/original/2X/3/3cdadd413c8599deb2c60f92e2c75acea5e3a389.png) **And now look what I get when i run the query:** ![image](https://epiusers.help/uploads/default/original/2X/6/6c011b4de1e862d3a3a8a084119ab7c7d49cff0d.png) **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** ![image](https://epiusers.help/uploads/default/original/2X/1/1f5101828b74bd83558b31d182f7496c4a43f5c0.png) ![image](https://epiusers.help/uploads/default/original/2X/e/e2933829ca46d9fa3be2343c21be50f7348b18ba.png) ![image](https://epiusers.help/uploads/default/original/2X/c/c29abd36caf74e23fa05a60bc0059a0511796bcb.png) **From SubQuery:** ![image](https://epiusers.help/uploads/default/original/2X/9/94b64e522483d89f7c60e77231ec7a042a90f22a.png) ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [March 19, 2019, 8:02pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/2 "2019-03-19T20:02:43Z")
Can you post the actual SQL query statement from the General tab? (Don’t forget to put it inside ````nohighlight ``` SQL ``` ```` ---
**Author:** ![tse.dba](https://www.epiusers.help/user_avatar/www.epiusers.help/tse.dba/32/8690_2.png) [@tse.dba](https://www.epiusers.help/u/tse.dba)\ **Post date:** [March 19, 2019, 8:03pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/3 "2019-03-19T20:03:57Z")
```auto 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] ``` ---
**Author:** ![Hogardy](https://www.epiusers.help/user_avatar/www.epiusers.help/hogardy/32/18785_2.png) [@Hogardy](https://www.epiusers.help/u/Hogardy)\ **Post date:** [March 19, 2019, 8:12pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/4 "2019-03-19T20:12:24Z")
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 ---
**Author:** ![tse.dba](https://www.epiusers.help/user_avatar/www.epiusers.help/tse.dba/32/8690_2.png) [@tse.dba](https://www.epiusers.help/u/tse.dba)\ **Post date:** [March 19, 2019, 8:17pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/5 "2019-03-19T20:17:25Z")
How do i look up the event log on baqs? ---
**Author:** ![Hogardy](https://www.epiusers.help/user_avatar/www.epiusers.help/hogardy/32/18785_2.png) [@Hogardy](https://www.epiusers.help/u/Hogardy)\ **Post date:** [March 19, 2019, 8:34pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/6 "2019-03-19T20:34:57Z")
It is on the server side, under Event viewer ![image](https://epiusers.help/uploads/default/original/2X/2/24bf6f95f3a019eb77cbab7038ef06ed7679a106.png) 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 ---
**Author:** ![tse.dba](https://www.epiusers.help/user_avatar/www.epiusers.help/tse.dba/32/8690_2.png) [@tse.dba](https://www.epiusers.help/u/tse.dba)\ **Post date:** [March 19, 2019, 8:38pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/7 "2019-03-19T20:38:07Z")
I dont have that access, I am a SaaS environment. I dont undertstand “viewing the results of the calculated field before using in formula” ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [March 19, 2019, 8:47pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/8 "2019-03-19T20:47:27Z")
> [@Hogardy](#): > > Is it possible that Calculated\_TotalIssued be zero? Then your error would be divide by zero… I second that. You need a case statement to handle 0’s. ---
**Author:** ![Adam](https://www.epiusers.help/letter_avatar_proxy/v4/letter/a/4af34b/32.png) [@Adam](https://www.epiusers.help/u/Adam)\ **Post date:** [March 19, 2019, 8:49pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/9 "2019-03-19T20:49:14Z")
One way to check for zero: (case when JobAsmbl.RequiredQty \> 0 then Diff/JobAsmbl.RequiredQty\*100 else 0 end) ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [March 19, 2019, 11:55pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/10 "2019-03-19T23:55:52Z")
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 ```auto Diff / (JobAsy.QtyRequired + 0.001) * 100 ``` Or better yet, do the check for QtyRequired being zero. ---
**Author:** ![tse.dba](https://www.epiusers.help/user_avatar/www.epiusers.help/tse.dba/32/8690_2.png) [@tse.dba](https://www.epiusers.help/u/tse.dba)\ **Post date:** [March 20, 2019, 8:09pm UTC](https://www.epiusers.help/t/query-formula-causing-bad-sql-statement/53735/11 "2019-03-20T20:09:25Z")
I think that did it.