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

[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!