Issue with selection criteria in query generator

[Andrii_Velhov]

I have create a report to track our purchase orders delivery performance (see the code below). The only issue is that I cannot add a filtration by vendor name into selection criteria.

SELECT
    PurchaseOrder."DocNum" AS "Purchase Order Number",
    PurchaseOrder."CardName" AS "Vendor Name",
    PurchaseOrderItem."LineNum" + 1 AS "Item Line #",  
    PurchaseOrderItem."ItemCode" AS "Item Code",
    ROUND(PurchaseOrderItem."Quantity") AS "Ordered Quantity",
    PurchaseOrderItem."ShipDate" AS "Scheduled Delivery Date",
    GoodsReceiptPO."DocNum" AS "Delivery Document Number",
    GoodsReceiptPO."DocDate" AS "Actual Delivery Date",
    ROUND(GoodsReceiptPOItem."Quantity") AS "Delivered Quantity",
    CASE
        WHEN GoodsReceiptPOItem."Quantity" = PurchaseOrderItem."Quantity" THEN 'Complete'
        WHEN GoodsReceiptPOItem."Quantity" > PurchaseOrderItem."Quantity" THEN 'Over Delivery'
        WHEN GoodsReceiptPOItem."Quantity" < PurchaseOrderItem."Quantity" AND GoodsReceiptPO."DocStatus" = 'C' THEN 'Partial Delivery'
        WHEN GoodsReceiptPO."DocStatus" != 'C' THEN 'Open Delivery'
        ELSE 'No Delivery'
    END AS "Delivery Status",
    CASE
        WHEN GoodsReceiptPO."DocDate" <= PurchaseOrderItem."ShipDate" THEN 'On Schedule'
        WHEN GoodsReceiptPO."DocDate" > PurchaseOrderItem."ShipDate" THEN 'Late'
        ELSE 'No Delivery'
    END AS "Timeliness",
    DAYS_BETWEEN(GoodsReceiptPO."DocDate", PurchaseOrderItem."ShipDate") AS "Days Diff",
    CASE
        WHEN GoodsReceiptPO."DocDate" <= PurchaseOrderItem."ShipDate" THEN GoodsReceiptPO."DocNum" ELSE NULL
    END AS "On Schedule",
    CASE
        WHEN GoodsReceiptPo."DocDate" > PurchaseOrderItem."ShipDate" THEN GoodsReceiptPO."DocNum" ELSE NULL
    END AS "Late",
    CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 1 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 6 THEN GoodsReceiptPO."DocNum" ELSE NULL END AS "< 7",
    CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 7 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 14 THEN GoodsReceiptPO."DocNum" ELSE NULL END AS "> 7 < 14",
    CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 15 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 28 THEN GoodsReceiptPO."DocNum" ELSE NULL END AS "> 15 < 28",
    CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 29 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 56 THEN GoodsReceiptPO."DocNum" ELSE NULL END AS "> 29 < 56",
    CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 57 THEN GoodsReceiptPO."DocNum" ELSE NULL END AS "> 57"
FROM 
    OPOR AS PurchaseOrder
INNER JOIN 
    POR1 PurchaseOrderItem ON PurchaseOrder."DocEntry" = PurchaseOrderItem."DocEntry"
LEFT JOIN 
    PDN1 GoodsReceiptPOItem ON PurchaseOrderItem."DocEntry" = GoodsReceiptPOItem."BaseEntry" AND PurchaseOrderItem."LineNum" = GoodsReceiptPOItem."BaseLine" 
LEFT JOIN
    OPDN GoodsReceiptPO ON GoodsReceiptPOItem."DocEntry" = GoodsReceiptPO."DocEntry"
WHERE 
    PurchaseOrderItem."LineStatus" = 'C'
    AND
    (GoodsReceiptPO."DocStatus" = 'C' AND GoodsReceiptPO."CANCELED" = 'N')
    AND
    (PurchaseOrder."CardName" = '[%0]' or '[%0]' = ' ')
    AND 
    ((GoodsReceiptPO."DocDate" >= '[%1]' or '[%1]' = ' ') AND (GoodsReceiptPO."DocDate" <= '[%2]' or '[%2]' = ' '))  
UNION ALL
SELECT
    COUNT(PurchaseOrder."DocNum") AS "Purchase Order Number",
    NULL AS "Vendor Name",
    NULL AS "Item Line #",
    NULL AS "Item Code",
    ROUND(SUM(PurchaseOrderItem."Quantity")) AS "Ordered Quantity",
    NULL AS "Scheduled Delivery Date",
    COUNT(GoodsReceiptPO."DocNum") AS "Delivery Document Number",
    NULL AS "Actual Delivery Date",
    ROUND(SUM(GoodsReceiptPOItem."Quantity")) AS "Delivered Quantity",
    NULL AS "Delivery Status",
    NULL AS "Timeliness",
    NULL AS "Days Difference",
    COUNT(CASE WHEN GoodsReceiptPO."DocDate" <= PurchaseOrderItem."ShipDate" THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "On Schedule",
    COUNT(CASE WHEN GoodsReceiptPO."DocDate" > PurchaseOrderItem."ShipDate" THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "Late",
    COUNT(CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 1 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 6 THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "< 7",
    COUNT(CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 7 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 14 THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "> 7 < 14",
    COUNT(CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 15 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 28 THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "> 15 < 28",
    COUNT(CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 29 AND DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") <= 56 THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "> 29 < 56",
    COUNT(CASE WHEN DAYS_BETWEEN(PurchaseOrderItem."ShipDate", GoodsReceiptPO."DocDate") >= 57 THEN GoodsReceiptPO."DocNum" ELSE NULL END) AS "Delay > 57"
FROM 
    OPOR AS PurchaseOrder
INNER JOIN 
    POR1 PurchaseOrderItem ON PurchaseOrder."DocEntry" = PurchaseOrderItem."DocEntry"
LEFT JOIN 
    PDN1 GoodsReceiptPOItem ON PurchaseOrderItem."DocEntry" = GoodsReceiptPOItem."BaseEntry" AND PurchaseOrderItem."LineNum" = GoodsReceiptPOItem."BaseLine" 
LEFT JOIN
    OPDN GoodsReceiptPO ON GoodsReceiptPOItem."DocEntry" = GoodsReceiptPO."DocEntry"
WHERE 
    PurchaseOrderItem."LineStatus" = 'C'
    AND
    (GoodsReceiptPO."DocStatus" = 'C' AND GoodsReceiptPO."CANCELED" = 'N')
    AND
    (PurchaseOrder."CardName" = '[%0]' or '[%0]' = ' ')
    AND 
    ((GoodsReceiptPO."DocDate" >= '[%1]' or '[%1]' = ' ') AND (GoodsReceiptPO."DocDate" <= '[%2]' or '[%2]' = ' '))

When the code is like this it gives me the following error:

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "FROM": line 27 col 1 'Received Alerts' (OAIB) (at pos 1447)

If I remove the

 AND
 (PurchaseOrder."CardName" = '[%0]' or '[%0]' = ' ')

everything starts working.

Any help is appreciated.

Thanks.

[Johan_H]

Hi Andrii,

B1's sql parser has difficulties parsing queries with sub queries and unions and such. You will find that drill-down arrows and the basic parameter syntax do not always work.

For your parameters, please try this syntax:

/* select * from OPOR x */
DECLARE @CardName AS NVARCHAR(100)
DECLARE @From AS DATETIME
SET @CardName = /* x.CardName */ '[%0]'
SET @fROM = /* X.DocDate */ [%1]

--your query here, using the parameters above

This assumes MS SQL. If you run HANA, you will have to adapt the syntax of the parameters.

Regards,

Johan

[Andrii_Velhov]

Hi Johan,

I did as you suggested and got the following error after selecting any option from Drop Down field:

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "DECLARE": line 2 col 1 'User-Defined Values' (CSHS) (at pos 30)

[LoHa]

Hi Andrii,

variables often struggle if the table name is like PurchasrOrder.CardName.

You can test if it is better to use T0.CardName /T1. and so on instead

I always use that kind for variable

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_RefDate */ '[%1]'

But it seems you use Hana, and I'am not sure if it works for you.

regards Lothar

[Andrii_Velhov]

Hi LoHa,

This seems to help a little. I have changed the whole query to use T0, T1, T2, T3 and started using declarations at the top. The selection criteria window started showing a Customer/Vendor Drop Down field but after I do the selection I get the following error:

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "DECLARE": line 2 col 1 'User-Defined Values' (CSHS) (at pos 30)

[LoHa]

Please show me your whole query again here

regards Lothar

[Andrii_Velhov]

/**SELECT FROM [OPDN] T0 **/
DECLARE @DocDateFrom AS DATETIME
/* WHERE */
SET @DocDateFrom = /* T0."DocDate" */ '[%0]'

/**SELECT FROM [OPDN] T1 **/
DECLARE @DocDateTo AS DATETIME
/* WHERE */
SET @DocDateTo = /* T1."DocDate" */ '[%1]'

/**SELECT FROM [OPOR] T2 **/
DECLARE @CardName AS NVARCHAR(100)
/* WHERE */
SET @CardName = /* T2."CardName" */ '[%2]'

SELECT
    T0."DocNum" AS "Purchase Order Number",
    T0."CardName" AS "Vendor Name",
    T1."LineNum" + 1 AS "Item Line #",  
    T1."ItemCode" AS "Item Code",
    ROUND(T1."Quantity") AS "Ordered Quantity",
    T1."ShipDate" AS "Scheduled Delivery Date",
    T3."DocNum" AS "Delivery Document Number",
    T3."DocDate" AS "Actual Delivery Date",
    ROUND(T2."Quantity") AS "Delivered Quantity",
    CASE
        WHEN T2."Quantity" = T1."Quantity" THEN 'Complete'
        WHEN T2."Quantity" > T1."Quantity" THEN 'Over Delivery'
        WHEN T2."Quantity" < T1."Quantity" AND T3."DocStatus" = 'C' THEN 'Partial Delivery'
        WHEN T3."DocStatus" != 'C' THEN 'Open Delivery'
        ELSE 'No Delivery'
    END AS "Delivery Status",
    CASE
        WHEN T3."DocDate" <= T1."ShipDate" THEN 'On Schedule'
        WHEN T3."DocDate" > T1."ShipDate" THEN 'Late'
        ELSE 'No Delivery'
    END AS "Timeliness",
    DAYS_BETWEEN(T3."DocDate", T1."ShipDate") AS "Days Diff",
    CASE
        WHEN T3."DocDate" <= T1."ShipDate" THEN T3."DocNum" ELSE NULL
    END AS "On Schedule",
    CASE
        WHEN T3."DocDate" > T1."ShipDate" THEN T3."DocNum" ELSE NULL
    END AS "Late",
    CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 1 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 6 THEN T3."DocNum" ELSE NULL END AS "< 7",
    CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 7 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 14 THEN T3."DocNum" ELSE NULL END AS "> 7 < 14",
    CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 15 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 28 THEN T3."DocNum" ELSE NULL END AS "> 15 < 28",
    CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 29 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 56 THEN T3."DocNum" ELSE NULL END AS "> 29 < 56",
    CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 57 THEN T3."DocNum" ELSE NULL END AS "> 57"
FROM 
    OPOR AS T0
INNER JOIN 
    POR1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN 
    PDN1 T2 ON T1."DocEntry" = T2."BaseEntry" AND T1."LineNum" = T2."BaseLine" 
LEFT JOIN
    OPDN T3 ON T2."DocEntry" = T3."DocEntry"
WHERE 
    T1."LineStatus" = 'C'
    AND
    (T3."DocStatus" = 'C' AND T3."CANCELED" = 'N')
    AND
    (T0."CardName" = @CardName or @CardName = ' ')
    AND 
    ((T3."DocDate" >= @DocDateFrom or @DocDateFrom = ' ') AND (T3."DocDate" <= @DocDateTo or @DocDateTo = ' '))  
UNION ALL
SELECT
    COUNT(T0."DocNum") AS "Purchase Order Number",
    NULL AS "Vendor Name",
    NULL AS "Item Line #",
    NULL AS "Item Code",
    ROUND(SUM(T1."Quantity")) AS "Ordered Quantity",
    NULL AS "Scheduled Delivery Date",
    COUNT(T3."DocNum") AS "Delivery Document Number",
    NULL AS "Actual Delivery Date",
    ROUND(SUM(T2."Quantity")) AS "Delivered Quantity",
    NULL AS "Delivery Status",
    NULL AS "Timeliness",
    NULL AS "Days Difference",
    COUNT(CASE WHEN T3."DocDate" <= T1."ShipDate" THEN T3."DocNum" ELSE NULL END) AS "On Schedule",
    COUNT(CASE WHEN T3."DocDate" > T1."ShipDate" THEN T3."DocNum" ELSE NULL END) AS "Late",
    COUNT(CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 1 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 6 THEN T3."DocNum" ELSE NULL END) AS "< 7",
    COUNT(CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 7 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 14 THEN T3."DocNum" ELSE NULL END) AS "> 7 < 14",
    COUNT(CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 15 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 28 THEN T3."DocNum" ELSE NULL END) AS "> 15 < 28",
    COUNT(CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 29 AND DAYS_BETWEEN(T1."ShipDate", T3."DocDate") <= 56 THEN T3."DocNum" ELSE NULL END) AS "> 29 < 56",
    COUNT(CASE WHEN DAYS_BETWEEN(T1."ShipDate", T3."DocDate") >= 57 THEN T3."DocNum" ELSE NULL END) AS "Delay > 57"
FROM 
    OPOR AS T0
INNER JOIN 
    POR1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN 
    PDN1 T2 ON T1."DocEntry" = T2."BaseEntry" AND T1."LineNum" = T2."BaseLine" 
LEFT JOIN
    OPDN T3 ON T2."DocEntry" = T3."DocEntry"
WHERE 
    T1."LineStatus" = 'C'
    AND
    (T3."DocStatus" = 'C' AND T3."CANCELED" = 'N')
    AND
    (T0."CardName" = @CardName or @CardName = ' ')
    AND 
    ((T3."DocDate" >= @DocDateFrom or @DocDateFrom = ' ') AND (T3."DocDate" <= @DocDateTo or @DocDateTo = ' '))