Skip to Content
avatar image
-1
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 01, 2017 at 08:49 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded