Skip to Content

query is working on sql but not on SAP B1

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]

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    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.

    capture.png (6.7 kB)
  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Mar 24, 2017 at 04:33 PM

    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

    Add comment
    10|10000 characters needed characters exceeded