query is working on sql but not on SAP B1

[datta1990]

Hi,

The following query is working on sql but not on SAP B1. Can anyone tell me the reason

Select A.[Supplier Number], A.[Supplier Name], A.[Item Code], A.[Manufacturer], A.[Value], A.[Qty] , A.[Invoice LineTotal] as 'Line Total' From ( SELECT T0.[CardCode] as 'Supplier Number', T0.[CardName] as 'Supplier Name', t1.[Itemcode] as 'Item Code', T3.[FirmName] as 'Manufacturer', T1.[Price]as'Value', T1.[Quantity]as 'Qty', T1.[LineTotal] as 'Invoice LineTotal' FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] Left Outer JOIN RPC1 T4 ON T4.BaseEntry = T1.DocEntry and T4.ItemCode = T1.ItemCode Left OUter Join ORPC T5 On T4.DocEntry = T5.DocEntry WHERE T0.[DocDate] between '2016/06/01' and '2016/06/30' AND T0.[CardCode] between 's0072' and 's0072' UNION ALL SELECT T5.[CardCode] as 'Supplier Number', T5.[CardName] as 'Supplier Name', T4.[Itemcode] as 'Item Code', T3.[FirmName] as 'Manufacturer', T4.Price as 'Value', T4.Quantity as 'Qty', - T4.[LineTotal] as 'Invoice LineTotal'--, FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] INNER JOIN RPC1 T4 ON T4.BaseEntry = T1.DocEntry and T4.ItemCode = T1.ItemCode INNER Join ORPC T5 On T4.DocEntry = T5.DocEntry WHERE T0.[DocDate] between '2016/06/01' and '2016/06/30' AND T0.[CardCode] between 's0072' and 's0072' ) A Order BY A.[Item Code]

[former_member555662]

when my condition is WHERE T0.[U_THF_AssortName] = [%0] gives me below error.

But when i use WHERE T0.[U_THF_AssortName] = 'DECEMBER_2016'

it works. I need to give an option to who ever using the query to select the Assortment name they want.

Please help.

[Former Member · Accepted solution]

I have tweaked yours slightly but it worked on SAP B1.

SELECT
A.[Supplier Number] as 'CardCode',
A.[Supplier Name] as 'CardName',
A.[ItemCode] as 'ItemCode',
A.[Manufacturer] as 'FirmName',
A.[Value] as 'Price',
A.[Qty] as 'Quantity',
A.[LineTotal] as 'LineTotal' FROM ( SELECT T0.[CardCode] as 'Supplier Number',
T0.[CardName] as 'Supplier Name',
T1.[ItemCode] as 'ItemCode',
T3.[FirmName] as 'Manufacturer',
T1.[Price] as 'Value',
T1.[Quantity] as 'Qty',
T1.[LineTotal] as 'LineTotal' FROM dbo.OPCH T0
INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode]
INNER JOIN RPC1 T4 ON T4.BaseEntry = T1.DocEntry and T4.ItemCode = T1.ItemCode
INNER JOIN ORPC T5 On T4.DocEntry = T5.DocEntry
WHERE T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.[CardCode] >= [%2]
UNION ALL SELECT T5.[CardCode] as 'Supplier Number',
T5.[CardName] as 'Supplier Name',
T4.[ItemCode] as 'Item Code',
T3.[FirmName] as 'Manufacturer',
T4.[Price] as 'Value',
T4.[Quantity] as 'Qty',
-T4.[LineTotal] as 'LineTotal' FROM dbo.OPCH T0
INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode]
INNER JOIN RPC1 T4 ON T4.BaseEntry = T1.DocEntry and T4.ItemCode = T1.ItemCode
INNER JOIN ORPC T5 On T4.DocEntry = T5.DocEntry
WHERE T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.[CardCode] >= [%2]
) A
Order By A.ItemCode