An odd situation with a BAQ that we have never seen before… In Analyze the BAQ says “Syntax is OK” but when testing we get an error - “Bad SQL statement”. Not sure if this is related to the fact that is it returning 0 rows or something else at run-time and the error is causing zero rows. The intent of the BAQ is to create a tab delimited text file with one line of header values and a second line of the data values. We eventually want the text field to be deposited in a secure FTP site , like a drop box, where BarTender can see it and process the label information. The SQL “not real” code under the General tab is long but we don’t see anything obvious in it. The actual table links are in a sub-report and the Top Level SubQuery has only the sub-report with no links to anything else and the display is one huge calculated field (see below) from the fields passed from the SubQuery.
Any ideas what the Bad SQL means even when the Syntax is OK? It says to check the Server Event Logs but I am not sure where that is for Cloud/SaaS environment.
Thanks,
-Todd C.
select
('Printer_c' + Char(7) + 'UserID_c' + Char(7) + 'Company' + Char(7) + 'SysDate' + Char(7) + 'TranNum' + Char(7) + 'PartNum' + Char(7) + 'WareHouseCode' + Char(7) + 'BinNum' + Char(7) + 'TranClass' + Char(7) + 'TranType' + Char(7) + 'InventoryTrans' + Char(7) + 'TranDate' + Char(7) + 'TranQty' + Char(7) + 'UM' + Char(7) + 'JobNum' + Char(7) + 'AssemblySeq' + Char(7) + 'JobSeq' + Char(7) + 'PONum' + Char(7) + 'POLine' + Char(7) + 'PORelNum' + Char(7) + 'WareHouse2' + Char(7) + 'BinNum2' + Char(7) + 'OrderNum' + Char(7) + 'OrderLine' + Char(7) + 'OrderRelNum' + Char(7) + 'EntryPerson' + Char(7) + 'PartDescription' + Char(7) + 'RevisionNum' + Char(7) + 'VendorNum' + Char(7) + 'PackSlip' + Char(7) + 'LotNum' + Char(7) + 'VendorNum' + Char(7) + 'VendorID' + Char(7) + 'Name' + Char(13) + SubQuery1.UD01_Printer_c + Char(7) + SubQuery1.UD01_UserID_c + Char(7) + SubQuery1.PartTran_Company + Char(7) + convert(varchar, SubQuery1.PartTran_SysDate, 101) + Char(7) + SubQuery1.PartTran_TranNum + Char(7) + SubQuery1.PartTran_PartNum + Char(7) + SubQuery1.PartTran_WareHouseCode + Char(7) + SubQuery1.PartTran_BinNum + Char(7) + SubQuery1.PartTran_TranClass + Char(7) + SubQuery1.PartTran_TranType + Char(7) + SubQuery1.PartTran_InventoryTrans + Char(7) + convert(varchar, SubQuery1.PartTran_TranDate, 101) + Char(7) + SubQuery1.PartTran_TranQty + Char(7) + SubQuery1.PartTran_UM + Char(7) + SubQuery1.PartTran_JobNum + Char(7) + SubQuery1.PartTran_AssemblySeq + Char(7) + SubQuery1.PartTran_JobSeq + Char(7) + SubQuery1.PartTran_PONum + Char(7) + SubQuery1.PartTran_POLine + Char(7) + SubQuery1.PartTran_PORelNum + Char(7) + SubQuery1.PartTran_WareHouse2 + Char(7) + SubQuery1.PartTran_BinNum2 + Char(7) + SubQuery1.PartTran_OrderNum + Char(7) + SubQuery1.PartTran_OrderLine + Char(7) + SubQuery1.PartTran_OrderRelNum + Char(7) + SubQuery1.PartTran_EntryPerson + Char(7) + SubQuery1.PartTran_PartDescription + Char(7) + SubQuery1.PartTran_RevisionNum + Char(7) + SubQuery1.PartTran_VendorNum + Char(7) + SubQuery1.PartTran_PackSlip + Char(7) + SubQuery1.PartTran_LotNum + Char(7) + SubQuery1.Vendor_VendorNum + Char(7) + SubQuery1.Vendor_VendorID + Char(7) + SubQuery1.Vendor_Name) as [Calculated_BartenderFormat]
from (select
[UD01].[Printer_c] as [UD01_Printer_c],
[UD01].[UserID_c] as [UD01_UserID_c],
[PartTran].[Company] as [PartTran_Company],
[PartTran].[SysDate] as [PartTran_SysDate],
[PartTran].[TranNum] as [PartTran_TranNum],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[WareHouseCode] as [PartTran_WareHouseCode],
[PartTran].[BinNum] as [PartTran_BinNum],
[PartTran].[TranClass] as [PartTran_TranClass],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[InventoryTrans] as [PartTran_InventoryTrans],
[PartTran].[TranDate] as [PartTran_TranDate],
[PartTran].[TranQty] as [PartTran_TranQty],
[PartTran].[UM] as [PartTran_UM],
[PartTran].[JobNum] as [PartTran_JobNum],
[PartTran].[AssemblySeq] as [PartTran_AssemblySeq],
[PartTran].[JobSeq] as [PartTran_JobSeq],
[PartTran].[PONum] as [PartTran_PONum],
[PartTran].[POLine] as [PartTran_POLine],
[PartTran].[PORelNum] as [PartTran_PORelNum],
[PartTran].[WareHouse2] as [PartTran_WareHouse2],
[PartTran].[BinNum2] as [PartTran_BinNum2],
[PartTran].[OrderNum] as [PartTran_OrderNum],
[PartTran].[OrderLine] as [PartTran_OrderLine],
[PartTran].[OrderRelNum] as [PartTran_OrderRelNum],
[PartTran].[EntryPerson] as [PartTran_EntryPerson],
[PartTran].[PartDescription] as [PartTran_PartDescription],
[PartTran].[RevisionNum] as [PartTran_RevisionNum],
[PartTran].[VendorNum] as [PartTran_VendorNum],
[PartTran].[PackSlip] as [PartTran_PackSlip],
[PartTran].[LotNum] as [PartTran_LotNum],
[Vendor].[VendorNum] as [Vendor_VendorNum],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name]
from Erp.PartTran as PartTran
inner join Ice.UD01 as UD01 on
UD01.Company = PartTran.Company
and ( UD01.UserID_c = @CurrentUserID )
left outer join Erp.Vendor as Vendor on
PartTran.Company = Vendor.Company
and PartTran.VendorNum = Vendor.VendorNum
where (PartTran.SysDate = @Today and (PartTran.SysTime >= @CurrentTime and PartTran.SysTime < Constants.CurrentTime+1100 ) and PartTran.TranType like 'PUR-%' and PartTran.EntryPerson = @CurrentUserID)) as SubQuery1