Issue with SQL Query in SAP B1 HANA – Date Range Parameter Not Working

[ThomasMi]

Hello everyone,

I'm working on a query in SAP Business One HANA and trying to use date range parameters so that I can select invoices within a specific period. However, I'm facing an issue where the query does not work as expected.

SELECT
OPCH."DocEntry" AS "Document ID",
OPCH."DocNum" AS "Invoice Number",
OPCH."DocDate" AS "Document Date",
OPCH."CardCode" AS "Partner Code",
OPCH."CardName" AS "Partner Name",
PCH1."ItemCode" AS "Item Code",
OITM."ItemName" AS "Item Name",
PCH1."AcctCode" AS "GL Account",
PCH1."OcrCode" AS "Cost Center",
PCH1."OcrCode2" AS "Division",
PCH1."OcrCode5" AS "Other Data",
PCH1."LineTotal" AS "Line Total Price"
FROM OPCH
JOIN PCH1 ON OPCH."DocEntry" = PCH1."DocEntry"
LEFT JOIN OITM ON PCH1."ItemCode" = OITM."ItemCode"
WHERE
OPCH."DocDate" BETWEEN '[%0]' AND '[%1]'
AND (PCH1."OcrCode" = '[%2]' OR '[%2]' IS NULL)
AND (PCH1."OcrCode" IS NOT NULL OR '[%3]' = 'Y')
ORDER BY OPCH."DocNum", PCH1."LineNum";

How should I correctly define these parameters for HANA SQL in SAP B1?

[SonTran]

Hi,

Just add table alias, then your query works

try this

SELECT
T0."DocEntry" AS "Document ID",
T0."DocNum" AS "Invoice Number",
T0."DocDate" AS "Document Date",
T0."CardCode" AS "Partner Code",
T0."CardName" AS "Partner Name",
T1."ItemCode" AS "Item Code",
T2."ItemName" AS "Item Name",
T1."AcctCode" AS "GL Account",
T1."OcrCode" AS "Cost Center",
T1."OcrCode2" AS "Division",
T1."OcrCode5" AS "Other Data",
T1."LineTotal" AS "Line Total Price"
FROM OPCH T0
JOIN PCH1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE
T0."DocDate" BETWEEN '[%0]' AND '[%1]'
AND (T1."OcrCode" = '[%2]' OR '[%2]' IS NULL)
AND (T1."OcrCode" IS NOT NULL OR '[%3]' = 'Y')
ORDER BY T0."DocNum", T1."LineNum";

[ThomasMi]

Thank you, it works now!