Problematic syntax error near “)” at line 14: SQL statement - SAP Hana

Question

I am sure that you are used to this question but nonetheless I am having trouble with my SQL script. It is very frustrating.

When I execute the SQL against the SAP Hana database, I continuously receive this error:

Errors occurred while executing the SQL statement: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ")": line 14 col 35

Let me present the SQL concerned here:

SELECT
  BKPF.BKTXT, BKPF.MONAT, BSEG.BELNR, BSEG.BUKRS, BSEG.DMBTR, BSEG.GJAHR, BSEG.MANDT, BSEG.PRCTR, BSEG.SAKNR, CEPCT.KTEXT, CEPCT.LTEXT, SKAT.MCOD1, SKAT.TXT20, SKAT.TXT50
FROM
  SAPPR1.BSEG
  INNER JOIN SAPPR1.BKPF ON BSEG.GJAHR = BKPF.GJAHR
  INNER JOIN SAPPR1.CEPCT ON BSEG.PRCTR = CEPCT.PRCTR
  INNER JOIN (SELECT
                SAKNR, TXT20, TXT50, MCOD1
              FROM
                SAPPR1.SKAT
              WHERE
                SPRAS not LIKE  '%[a-z0-9 .]%' ) AS SKAT_SUB ON BSEG.SAKNR = SKAT_SUB.SAKNR
WHERE
  BKPF.MONAT = (SELECT Month('?')-1)
  AND (BSEG.GJAHR = (SELECT Year('?')-1 HAVING Month('?')-1 < 4) OR BSEG.GJAHR = (SELECT Year('?') HAVING Month('?')-1 > 3))
  AND BSEG.MANDT = ?
;

You really wanted to write HAVING in the second line of the where condition? Perhaps WHERE is the right word here. — Kapitany
Yes, I am sure about that part. I am using date functions here. — Kirin
@jarlh I need an answer that would be of help to me for now though — Kirin
Can you explain from a business point of view what you want to reach with your restrictions on MONAT and JAHR? And what are the questions marks in the restrictions? Is that statement taken from client which feeds some values via parameters? — Florian Pfeffer

Lars Br. Lars Br. · Accepted Answer · 2021-03-05T10:31:23

Unlike other DBMS HANA does not support selecting records out of nothing. A SELECT without a FROM is not valid.

In order to create a single-row set one can use the DUMMY table.

SELECT current_date FROM DUMMY;

creates the single-row set with the result of the function expression.

Having said that, for comparisons a set is not required. Instead the function can be directly put into the expression:

WHERE
  BKPF.MONAT = (MONTH(?)-1)

Also note that for string typed bind variables no quotation marks are required or allowed.

Problematic syntax error near “)” at line 14: SQL statement - SAP Hana

1 Answers