Error when adding date parameter to SAP B1 Query

former_member609818 — Tue, 08 Oct 2019 18:26:16 GMT

Hello all, I am trying to create a query between two databases (one non SAP) that are on the same server that includes a date parameter.

The query on its own runs fine, however, whenever I try to add a date parameter ( Using T1.FileDateB) to the query from the non SAP Database, I receive the following error

'Blanket Agreement' (OOAT)

I have included the query below. Any help is greatly appreciated.

select distinct(T1.transid) ,

T1.vendor as 'Vendor',

T1.vendorstatus as 'Vendor Status',

T1.FileDate as 'File Date',

T1.VendorFileName as 'File Name',

Case when isnull(T1.TargetDevice,'')<>'' then T1.TargetDevice else T1.ItemCode end '3rd Party ItemCode',

T1.Targetdevice as '3rd Party Target Device',

T1.itemcode '3rd party ItemCode',

T0.itemcode as 'SAP Item Code',

T1.LotNum '3rd Party Lot',

T0.DistNumber as 'SAP Lot Number',

T1.ParentLot '3rd Party Parent Lot',

cast(T0.Notes as nvarchar) 'SAP ParentLot',

T1.RecvQty '3rd Party Received Qty',

T0.Quantity-T0.Quantout 'SAP Onhand',

((T1.recvQty)-(T0.Quantity-T0.QuantOut)) as 'Variance',

T1.CurrentQty as '3rd Party Quantity',

T1.Stage as '3rd Party Stage',

Case when T5.QryGroup1 = 'Y' then 'FAB' when T5.QryGroup2 = 'Y' then 'FAB' when T5.QryGroup3 = 'Y' then 'EWS' when T5.QryGroup4 = 'Y' then 'ASSEM' when T5.QryGroup5 = 'Y' then 'FT' when T5.QryGroup6 = 'Y' then 'FG' when T5.QryGroup7 = 'Y' then 'FG' when T5.QryGroup8 = 'Y' then 'FAB' When T5.Qrygroup9 = 'Y' then 'EWS' end as 'SAP Stage' , T5.FrgnName as 'SAP Item Family', Case when (T0.Quantity-T0.Quantout) = '0' then '0' --else '0' end as 'Price Per Unit' else (T0.CostTotal/T0.Quantity) end as 'SAP Price Per Unit'

from TEST.dbo.THIRD_PARTY_FILES T1 with(nolock)

left join SAPDATABASE.dbo.OBTN T0 With(nolock) on T0.DistNumber = T1.LotNum collate Database_default and T0.ItemCode = Case when isnull(T1.TargetDevice,'')<>'' then T1.TargetDevice else T1.ItemCode end collate database_default left join SAPDATABASE.dbo.ITL1 T3 with(nolock) on T3.ItemCode = T0.itemcode and T3.SysNumber = T0.SysNumber

Left Join SAPDATABASE.dbo.OITL T4 with(nolock) on T4.LogEntry = T3.LogEntry

Left join SAPDATABASE.dbo.OITM T5 with(noLock) on T5.itemcode = T1.itemcode collate Database_default

Where T1.RecvQty <> 0 and T1.IsValid = 'Y' and -- and (T0.Quantity - T0.QuantOut) = T1.RecvQty and (T0.Quantity - T0.QuantOut) <> '0' where T1.filename = '[%0]' Order By T1.transid asc

topic Error when adding date parameter to SAP B1 Query in Application Development and Automation Discussions

Error when adding date parameter to SAP B1 Query