BAQ Error "Bad SQL"

Epicor ERP 10
,
2

@josecgomez hey look same problem we just had! But wait… the customer table is involved in this one too, coincidence? Perhaps not!

We just troubleshot a query where if you joined InvcHead to Customer via Company and CustNum the query would time out and say “Bad SQL” remove the customer table all good. Funny thing that’s a native index so nothing weird there. Not sure if Jose ever found out exactly what was going wrong, but running the query from the Execution Plan or the Designer window it was fine, as the BAQ not fine.

3

Which Version are you all seeing this on? I can test see if it occurs in older vs newer.

4

Bah, why didn’t I think of that. The last 2 queries I was stuck on for a long time were also crashing because of Company. Deleted all company from the joins and it works fine now.

5

MT SaaS, we just got upgraded to 10.2.500.2 not too long ago.

1 Like
6

geez I wonder how many of my BAQs would break on 10.2.500.2 if I had this bug :slight_smile: I thought Epicor used ATE and Unit Tests to test each build, but we often find non-trivial bugs that could have been avoided (yeah I know im blaming .2 it could also be Data specific).

7

Its not every BAQ with company in it, in fact none of my existing BAQ’s broke. Its something to do with the specific tables and joins used or something? Maybe @jgiese.wci can tell you what the exact criteria is, but its been a long standing issue that company messes stuff up sometimes.

1 Like
8

I just ran your BAQ in 10.2.400.10 - Just imported it ran a small and a large date range, instant results, no error. Ran in 40ms for 400 rows.

2 Likes
9

I think I might have found another problem? This super simple one also gives a bad SQL error and it has no join with customer…

image

select 
	[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
	[Task].[TaskID] as [Task_TaskID],
	[Task].[TaskSeqNum] as [Task_TaskSeqNum],
	[Task].[TaskDescription] as [Task_TaskDescription],
	[Task].[Conclusion] as [Task_Conclusion],
	[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on 
	QuoteHed.QuoteNum = Task.Key1
10

in 10.2.400.10 worked no error.

Query returned 1235 row(s).
Query has no more records to return.
Query execution total time: 287.0886 ms.

select 
	[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
	[Task].[TaskID] as [Task_TaskID],
	[Task].[TaskSeqNum] as [Task_TaskSeqNum],
	[Task].[TaskDescription] as [Task_TaskDescription],
	[Task].[Conclusion] as [Task_Conclusion],
	[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on 
	QuoteHed.QuoteNum = Task.Key1
2 Likes
11

These types are different. Should you not cast the QuoteNum to varchar?

12

I’ll try that now, but @hkeric.wci didn’t have to it seems.

13

Yeah, I saw that. Strange. Whenever I connect an integer to one of those KEY# fields, I usually cast it.

14

This worked:

select 
	[Task].[TaskID] as [Task_TaskID],
	[Task].[TaskSeqNum] as [Task_TaskSeqNum],
	[Task].[TaskDescription] as [Task_TaskDescription],
	[Task].[Conclusion] as [Task_Conclusion],
	[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.Task as Task
inner join  (select 
	(convert(varchar, QuoteHed.QuoteNum)) as [Calculated_calcq],
	[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum]
from Erp.QuoteHed as QuoteHed
group by [QuoteHed].[QuoteNum])  as SubQuery2 on 
	Task.Key1 = SubQuery2.Calculated_calcq

Is that how you normally cast it?

But the BAQ I had in OP works without the casting. Very strange… so sometimes you have to cast and other times you do not.

15

Yes and, I can’t tell you man.

:man_shrugging:

1 Like
16

Tables that use key1, key2, key3, etc… might have a single data type as key1, and if its’s the right type, no casting needed.

But in a table like TranGLC, Key1 can be different types. The type depends on the RelatedToFile field.

If RelatedToFile = PartTran, than Key 1 is a date. For Most others its something that looks an integer

If you look at the BAQ Results, notice that the numbers are left aligned. Meaning they are really text strings.

image

1 Like
17

I get that part, but if Key1 is actually a string why would it ever work without casting (not counting string ↔ string joins)? For example in my original BAQ I had:

And it works, but the second BAQ I posted, I used the same fields and it did not work, until casting was added. That was the cherry on top as far as confusion goes.

18

Hi Evan,

Task.Key1 is NVarchar(50) and QuoteHed.QuoteNum is Numeric.

So you need to cast the QuoteNum as NVarchar(50) to make it similar to Task.Key1

On your BAQ you should have something like this:

select
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[Task].[TaskID] as [Task_TaskID],
[Task].[TaskSeqNum] as [Task_TaskSeqNum],
[Task].[TaskDescription] as [Task_TaskDescription],
[Task].[Conclusion] as [Task_Conclusion],
[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on
Task.Key1 = Convert(NVARCHAR(50), QuoteNum)

So on the inner join definition, add the following expression: Convert(NVARCHAR(50), QuoteNum)

image
image.png775×629 33.6 KB

3 Likes
19

Ah, that is a better way of doing it. I made a sub query with a calculated field and then joined with that, but your way looks less messy.

Welcome to the forums by the way.

2 Likes
20

Yes, sorry, I do the join like @Jorge_Gracia.

21

I always use the SQL ANSI Standard one, old habits.

CAST(QuoteNum as nvarchar(52))

But yes for you it may have been data, maybe I just dont have any that mix. I also just define it in the JOIN.

Even when you want to specify a specific let’s say “RelatedToFile” you can simply type it into the JOIN

'SomeTable'

Just because its not in the drop-down, doesnt mean it won’t accept it :smiley:

2 Likes