Query error when adding a variable [%0]

[OmarJ_]

Hello experts.

I receive an error when using a variable in my query in SAP Business One Hana.

The following query works as expected, but when I change the date ('2025-08-31') in the where clause for a variable ([%0]) it gives an error: 1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "UNION": line 1 col 1 'Job de programación back end' (OBSJ) (at pos 1)

SELECT
'RAZONES DE LIQUIDEZ' AS "Razón financiera",
'' AS "Razón",
'' AS "Cuenta",
NULL AS "Monto",
'' AS "Cuenta 2",
NULL AS "Monto"
FROM DUMMY

UNION ALL

SELECT
'Razón corriente' AS "Razón financiera",

TO_VARCHAR(
TO_DECIMAL(
SUM(
CASE
WHEN T1."FatherNum" IN ('101','102','103','104','105','106','107','108','109')
THEN T0."Debit" - T0."Credit"
ELSE 0
END
)
/
NULLIF(
SUM(
CASE
WHEN T1."FatherNum" IN ('203','204','204-1','204-2','205','207')
THEN T0."Credit" - T0."Debit"
ELSE 0
END
), 0
)
, 18, 4)
) AS "Razón",

'Activo corriente' AS "Cuenta",
SUM(
CASE
WHEN T1."FatherNum" IN ('101','102','103','104','105','106','107','108','109')
THEN T0."Debit" - T0."Credit"
ELSE 0
END
) AS "Monto",

'Pasivo corriente' AS "Cuenta 2",
SUM(
CASE
WHEN T1."FatherNum" IN ('203','204','204-1','204-2','205','207')
THEN T0."Credit" - T0."Debit"
ELSE 0
END
) AS "Monto"

FROM "JDT1" T0
INNER JOIN "OACT" T1 ON T0."Account" = T1."AcctCode"
WHERE
T0."RefDate" >= '2020-01-01'
AND T0."RefDate" <= '2025-08-31'

I need to use a variable [0%] instead of  the date  '2025-08-31'

When I try using the variable without the select from dummy query at the beginning, it works

I tried many ways with IA, but still can't make it work.
Thanks in advance.

[SudheerKumarCH]

Hi Omar, Can you share me the details of your design? are you trying to declare varaibales in HANA SQL Query and use it in a procedure?it for dyn

[inga_babco2 · Accepted solution]

Write the following comment at the beginning of your query:

/*
SELECT
FROM "JDT1" T0
WHERE T0."RefDate" <= '[%0]'
*/

This comment will help SAP understand which SAP field the parameter [%0] belongs to, what the parameter means, and what heading the parameter should have.

Then you can use the parameter '[%0]' in the query:

[OmarJ_ · Accepted solution]

How did you found out about this "trick"? It worked! thank you so much!

[Johan_Hakkesteegt · Accepted solution]

You are welcome, this mechanic has been in B1 since at least version 8, and perhaps even longer. Courses and documentation unfortunately just do not mention it, I do not know why not.

[Johan_Hakkesteegt]

Hi Omar,

When using UNION and/or embedded queries and/or cross database tables, the sql parser does not (always) know how to parse the normal B1 query parameters.

In this case you can use the following syntax:

/* select * from JDT1 x */
DECLARE @DATEPARAMETER AS DATETIME
SET @DATEPARAMETER = /* x.RefDate */ [%0]

--your query here
--using @DATEPARAMETER
--where you would use [%0]

Regards,

Johan

[OmarJ_]

Thanks for the help Johan!