Bad sql statement

Epicor ERP 10
1

Hello Team,
May i know main reasons for BAD SQL STATEMENT error in BAQ report? I suspect there is more than one. What if there are lots of data and join is OK? Same query when copied in SQL Server run perfectly.

Regards,

2

There are many potential reasons, does the BAQ(s) behind the report execute w/o issue?

3

Not sure if it would present as “BAD SQL STATEMENT”, but a common problem that seems to pop-up on reports that normally run fine, is when a calculated field has a divide by zero. Maybe 99.9% of your data results in the divisor never being zero. But that 0.1% of the time, there would be an error.

Run the report again with different data (use different inputs (different date, customer, part, class, etc…). If it runs okay, then narrow it down to what is in your data that is causing the problem. Is it a specific date range, customer, part, Invoice Num, etc…

3 Likes
4

This is a little late, but in case someone ends up here through a search engine like I did, in addition to the division by zero error I have also gotten the Bad SQL Statement for:

  1. Null values in calculated fields - I fix these by using an if/then to set the value if the field is null (example: (case when SubQuery6.Calculated_cost is null then 0 else SubQuery6.Calculated_cost end)
  2. There is a data type/mismatch in your dataset. I had this one time when I had a date field and one job’s start date value was actually a date/time. We also had one that was imported with 0220 as the year instead of 2020, which wasn’t recognized as a valid date.
  3. Your nvarchar field is not long enough for some of the field values. It doesn’t recognize this as a problem until it tries to run the data through it.
  4. One of your nvarchar calculated fields has an integer in it. You have to use the conversion to make the integer a string - this usually happens for PO or Sales Order numbers, lines and releases. (example: convert(varchar, OrderRel.OrderNum))
5 Likes
5

Thanks Rabello,
For my case, Bad SQL Statement was a result of null value in the join statement. I added a criteria to prevent null values and problem solved.

Regards

1 Like
6

We had the problem because of a BAQ where we were joining Memos to POs. Memo Key1 is string and PO is integer so we had to write a convert function to fix the issue.

image
image992Ă—636 155 KB