on 2024 Mar 05 6:43 PM
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.
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
Please show me your whole query again here
regards Lothar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
/**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 = ' '))
User | Count |
---|---|
96 | |
12 | |
10 | |
8 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.