Skip to Content
-1

SAP HANA (Procedure) - Error when dividing count by declared variable

Oct 31, 2017 at 03:48 PM

71

avatar image
Former Member

Hello.

I am trying to run the following function:

DECLARE Respondents INTEGER := 2;
SELECT
QA.QID AS Question,
QA.ANSWER AS Answer,
COUNT(DISTINCT(QA.RESPID))/(:Respondents) AS Total
FROM QUESTION_ANSWERS QA
INNER JOIN RESPONDENT R ON R.RESPID = QA.RESPID
GROUP BY QA.QID, QA.ANSWER
ORDER BY QA.QID;

However the following error is shown:

SAP DBTech JDBC: [467]: cannot use parameter variable: RESPONDENTS: line 4 col 29 (at pos 79)

How can I make use of my declared variable? This variable will actually be set by a function call that returns an INTEGER. I did find similar error messages being discussed, but not directly related to my problem.Thank you,

Stefanno Da Silva

10 |10000 characters needed characters left characters exceeded

Which HANA revision you are using? I tested it on a HANA 1.0 SPS12 and a HANA 2.0 SPS02 system with success.

1
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Nov 01, 2017 at 08:49 AM
0

When you want to use SQLScript in the SQL console editor, you will have to enclose it into an anonymous block:

DO BEGIN   

    ... your code here ...

END;

When you do that with your code, you don't get the error.

In order to just run the statement without an anonymous block and to get prompted for the parameter, you cannot use the named parameter, but instead, you have the option to replace every parameter with a question mark (?).
The SQL console will then prepare the statement and provide an input field for every ? in the statement.
Note, that in this case, you're not using SQLScript but plain SQL.

Share
10 |10000 characters needed characters left characters exceeded