BAQ - Severity: Error, Text: Bad SQL statement

Kinetic ERP
1

Hi

Trying to build a relatively simple BAQ to link APInvDtl back to GLJrnDtl

image
image926×108 5.14 KB

Unsure if these joins make sense. Removing GLJrnDtl and it works. Adding in GLJrnDtl and see a BAD SQL statement error. Analyze query show nothing wrong. And there are no divide by zero issues (no doing any calculations)

Really looking for a way to add/replicate the Entity Description field under Chart Tracker → Transaction Detail in the Specific Transaction Detail grid. Mostly because the AP Header detail posted to GL is too coarse for the accountants and they would like to see the underlying AP invoice line details.

image

Amending posting rules to change posted text is relatively easy but it does add significantly more text which is unnecessary in the GL.

Thanks

1 Like
2

That join between the two table will be the problem… What fields are you joining on and do you have any more detail in the error.

Can you share the query summary?

3

What error is written in the server log?

4

If you hit Analyze, instead of Test, you should get some sort of warning that might help you understand what’s wrong.

5

IIRC there isn’t an automatically populated join between TranGLC and GLJrnDtl. What are your join conditions?

It also looks like you have some criteria associated to the table. Maybe one of those is angering SQL?

6

Hi

Points to note:

7

Query summary is roughly

select 
    [TranGLC].[RecordType] as [TranGLC_RecordType],
	[TranGLC].[IsModifiedByUser] as [TranGLC_IsModifiedByUser],
	[TranGLC].[MovementNum] as [TranGLC_MovementNum],
	[TranGLC].[MovementType] as [TranGLC_MovementType],
	[TranGLC].[Plant] as [TranGLC_Plant],
	[APInvDtl].[Description] as [APInvDtl_Description],
	[APInvDtl].[Company] as [APInvDtl_Company],
	[APInvDtl].[VendorNum] as [APInvDtl_VendorNum],
	[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
	[APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
	[APInvDtl].[LineType] as [APInvDtl_LineType],
	[APInvDtl].[UnitCost] as [APInvDtl_UnitCost],
	[APInvDtl].[DocUnitCost] as [APInvDtl_DocUnitCost],
	[GLJrnDtl].[JournalNum] as [GLJrnDtl_JournalNum],
	[GLJrnDtl].[JournalLine] as [GLJrnDtl_JournalLine],
	[GLJrnDtl].[JEDate] as [GLJrnDtl_JEDate],
	[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
	[GLJrnDtl].[GLAccount] as [GLJrnDtl_GLAccount],
	[GLJrnDtl].[DebitAmount] as [GLJrnDtl_DebitAmount],
	[GLJrnDtl].[CreditAmount] as [GLJrnDtl_CreditAmount]
from Erp.APInvDtl as APInvDtl
inner join Erp.APInvExp as APInvExp on 
	APInvDtl.Company = APInvExp.Company
	and APInvDtl.VendorNum = APInvExp.VendorNum
	and APInvDtl.InvoiceNum = APInvExp.InvoiceNum
	and APInvDtl.InvoiceLine = APInvExp.InvoiceLine
inner join Erp.TranGLC as TranGLC on 
	APInvExp.Company = TranGLC.Company
	and APInvExp.VendorNum = TranGLC.Key1
	and APInvExp.InvoiceNum = TranGLC.Key2
	and APInvExp.InvoiceLine = TranGLC.Key3
	and APInvExp.InvExpSeq = TranGLC.Key4
	and ( TranGLC.UserCanModify = false  )

inner join Erp.GLJrnDtl as GLJrnDtl on 
	TranGLC.Company = GLJrnDtl.Company
	and TranGLC.Key1 = GLJrnDtl.VendorNum
	and TranGLC.Key2 = GLJrnDtl.APInvoiceNum
	and TranGLC.JournalNum = GLJrnDtl.JournalNum
	and TranGLC.JournalLine = GLJrnDtl.JournalLine
	and ( GLJrnDtl.Company = '19261'  and GLJrnDtl.FiscalYear = 2025  and GLJrnDtl.FiscalPeriod = 7  )

I do get data back (limited to 10k records) in BAQ. I see a 400 or 404 error trying to run same as Odata.Feed in excel and get nothing.

I can’t see the server logs as I’m a MT cloud customer. Might have to lodge a support ticket but Analyze show “Syntax is OK”

Thanks

8

You might want to filter the TranGLC - that could be causing the issue.
I just tested with the two tables they ran.

from Erp.TranGLC as [TranGLC]
inner join Erp.GLJrnDtl as [GLJrnDtl] on
TranGLC.Key1 = GLJrnDtl.VendorNum
and TranGLC.Key2 = GLJrnDtl.APInvoiceNum
and TranGLC.JournalNum = GLJrnDtl.JournalNum
and TranGLC.JournalLine = GLJrnDtl.JournalLine
where (TranGLC.RelatedToFile = ‘ApInvExp’)

1 Like
9

You might want to review this KB with regards to joining to the tranglc as well

KB0039633

10

Thank you.

That certainly helps in understanding the joins better and the need for NVARCHAR between TranGLC and underlying tables.

1 Like