BAQ Bad SQL Statement from Table Criteria

Epicor ERP 10
1

I have a fairly simple BAQ that is returning a Bad SQL Statement error when testing it. I have criteria set on my InvcDtl table for PartNum <> 123 constant (123 being subbed out for the real PartNum in this example) AND PartNum <> 1234 constant (Again 1234 being subbed out for the real PartNum in this example). When I remove these the BAQ runs and returns data, which it should return data with or without these criteria.

image
image.png861×71 2.67 KB

I also have criteria on the Customer table for CustID = Cust1 constant (Cust1 being subbed for real customer name in this example). When I remove just this criteria the BAQ runs even when leaving the criteria on the InvcDtl table.

I checked the calculated field and it is not affecting this in any way. My links are also correct. I am stuck on this for now and I am not sure if this is a bug in Epicor 10.2.300.13 or if there is something going on in the background that I am unaware of.

Here is my Code:

select
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcDtl].[DocExtPrice] as [InvcDtl_DocExtPrice],
[InvcDtl].[DocDiscount] as [InvcDtl_DocDiscount],
[InvcDtl].[DocTotalMiscChrg] as [InvcDtl_DocTotalMiscChrg],
((InvcDtl.DocExtPrice) - (InvcDtl.DocDiscount) + (InvcDtl.DocTotalMiscChrg)) as [Calculated_LineTotal],
[ShipTo].[Name] as [ShipTo_Name],
[ShipTo].[Address1] as [ShipTo_Address1],
[ShipTo].[City] as [ShipTo_City],
[ShipTo].[State] as [ShipTo_State],
[ShipTo].[ZIP] as [ShipTo_ZIP],
[Customer].[CustID] as [Customer_CustID],
[InvcDtl].[XPartNum] as [InvcDtl_XPartNum],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[OTSState] as [InvcDtl_OTSState],
[InvcDtl].[SellingShipQty] as [InvcDtl_SellingShipQty]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
and ( InvcDtl.PartNum <> ‘123’ and InvcDtl.PartNum <> ‘1234’ )

inner join Erp.ShipTo as ShipTo on
InvcDtl.Company = ShipTo.Company
and InvcDtl.CustNum = ShipTo.CustNum
and InvcDtl.ShipToNum = ShipTo.ShipToNum
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
and InvcHead.CustNum = Customer.CustNum
and ( Customer.CustID = ‘Cust1’ )

where (InvcHead.Posted = TRUE)

Here is my calculated field if you need it (It is a Decimal with the format: ->>>,>>>,>>9.99):
(InvcDtl.DocExtPrice) - (InvcDtl.DocDiscount) + (InvcDtl.DocTotalMiscChrg)

2

What does the error read?

3

The quotes around your parts and custid don’t look right. In a BAQ, you don’t need to add them (if you did).

4

I would expect your criteria to appear in the SQL after a “where”, but they look like join conditions here.

Are you entering those in the criteria details for each table in the BAQ designer?

5

Hi Joshua,

One thing sticks out in your BAQ for me. It’s using the ShipTO table. I have struggled with this table in queries since we went on Epicor. For a Cust Num, Ship to num can be blank. Is it possible that the join on the InvcDtl.Ship to num to ShipTo.ShipToNum is your problem?

Nancy

6

The link from InvcDtl.ShipToNum to ShipTo.ShipToNum seems to be causing my issue. Thank you!

1 Like