BAD SQL from Display Table Field

Epicor ERP 10
1

good morning.
I am getting a bad sql error when DISPLAYING any field from a connected table, specifically the GLAcctDisp

I understand, typically, BAQ SQL is a result of bad data/calculation.
But I don’t get why, there is NO error, when the table is connected and i do NOT display any fields from it.
The error occurs ONLY when i try to add ANY field from that table into the display - including company.

is there something else occurring deep down on a table join when a display field is requested (besides the obvious show the field)

anyone experience this?
TIA

2

for those so inclinded - the SQL

select 
	[SalesTer].[TerritoryID] as [SalesTer_TerritoryID],
	[SalesTer].[TerritoryDesc] as [SalesTer_TerritoryDesc],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[APInvHed].[sgcPGOrder_c] as [APInvHed_sgcPGOrder_c],
	[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
	[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
	[APInvHed].[ApplyDate] as [APInvHed_ApplyDate],
	[APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
	[TranGLC].[BookDebitAmount] as [TranGLC_BookDebitAmount],
	[APInvHed].[DueDate] as [APInvHed_DueDate],
	[APInvDtl].[Description] as [APInvDtl_Description],
	[PurTerms].[Description] as [PurTerms_Description],
	[APInvHed].[REFPONum] as [APInvHed_REFPONum],
	[APInvDtl].[AllocDeferred_c] as [APInvDtl_AllocDeferred_c],
	[APInvHed].[EntryPerson] as [APInvHed_EntryPerson],
	[APInvHed].[Company] as [APInvHed_Company],
	[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
	[Vendor].[GroupCode] as [Vendor_GroupCode],
	[TranGLC].[GLAccount] as [TranGLC_GLAccount],
	[GLAcctDisp].[GLAcctDisp] as [GLAcctDisp_GLAcctDisp],
	[GLAcctDisp].[AccountDesc] as [GLAcctDisp_AccountDesc],
	[GLAcctDisp].[GLShortAcct] as [GLAcctDisp_GLShortAcct]
from Erp.APInvHed as APInvHed
inner join Erp.APInvDtl as APInvDtl on 
	APInvHed.Company = APInvDtl.Company
	and APInvHed.VendorNum = APInvDtl.VendorNum
	and APInvHed.InvoiceNum = APInvDtl.InvoiceNum
	and ( APInvDtl.AllocDeferred_c = True  )

left outer join Erp.APInvExp as APInvExp on 
	APInvExp.Company = APInvDtl.Company
	and APInvExp.VendorNum = APInvDtl.VendorNum
	and APInvExp.InvoiceNum = APInvDtl.InvoiceNum
	and APInvExp.InvoiceLine = APInvDtl.InvoiceLine
left outer join Erp.TranGLC as TranGLC on 
	APInvExp.Company = TranGLC.Company
	and APInvExp.VendorNum = TranGLC.Key1
	and APInvExp.InvoiceLine = TranGLC.Key3
	and APInvExp.InvoiceNum = TranGLC.Key2
	and APInvExp.InvExpSeq = TranGLC.Key4
	and ( TranGLC.RelatedToFile = 'APinvExp'  and TranGLC.RecordType = 'R'  )

left outer join Erp.GLAcctDisp as GLAcctDisp on 
	TranGLC.Company = GLAcctDisp.Company
	and TranGLC.COACode = GLAcctDisp.COACode
	and TranGLC.GLAccount = GLAcctDisp.GLAccount
inner join Erp.Vendor as Vendor on 
	APInvDtl.Company = Vendor.Company
	and APInvDtl.VendorNum = Vendor.VendorNum
left outer join Erp.PurTerms as PurTerms on 
	APInvHed.Company = PurTerms.Company
	and APInvHed.TermsCode = PurTerms.TermsCode
left outer join Erp.SalesTer as SalesTer on 
	APInvHed.Company = SalesTer.Company
	and APInvHed.sgcTerritoryID_c = SalesTer.TerritoryID
where (APInvHed.Posted = True)
3

Since our upgrade to 10.2.300, Bad SQL statement that is all we get usually… The error will be fully detailed in the event viewer on your Epicor server…

pierre

1 Like
4

right. unfortunately, accessing the event viewer is difficult for me. thx

however, i have requested a review of the log from EMS

5

I’m sure you’ve tried this, but if you run a BAQ with only the table that is giving you problems, do you get the same error?

1 Like
6

yes, did that - thx
it runs like a new chevy - all by itself

7

Did you try with the joins all changed to inner? (I know it’s not what you need, but sometimes they cause problems)

1 Like
8

It appears changing the GLACCTDISP to an inner join eliminates the error
WTF? i wonder why. i am checking to ensure i get the same result set

Thank you Brandon and Pierre

9

Post NOTE:
the result sets are NOT the same - although the Inner join provides 4 more entries. (1986 v 1990) GL accounts must have a display record, so i am not thinking it’s not a problem. But i will do some more testing and provide update if needed.

thanks again!

1 Like
10

OK - Here is the skinny. Error due to execution timeout

The inner join had the effect of reducing the execution time.
When i change the BAQ execution setting ‘query timeout’ to 40000000
the inner join and the original query (with fields)
(a) provide the SAME number of records and
(b) complete without ERROR

during testing i noticed error conditions around when over 30000 ns of execution time. and although I THOUGHT i had changed the ‘query timeout’
i suppose the number - it wasn’t big enough.

which also explains WHY the sometimes when i removed some of the non-essential tables the results were error free.
it just so happened the GLACCTDISP was the last table -
and the straw that broke the camels back.

i suppose if i had ready access to the event viewer it would have been quickly apparent … but i don’t.

So problem solved and mystery solved

Thanks again to you Brandon and Pierre
and to this forum!

1 Like