Iteration over SQL result in SQLScript procedure has an error

[Former Member]

Hello,

I want to iterate over the resultset of a sql select in a procedure. I tried  the example code from http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

unfortunately it does not work

Here is my procedure code, I'm doing a more or less basic SQL Select with a where and group_by.

CREATE PROCEDURE _SYS_BIC.reduce_bdd(
          in profileId VARCHAR(100)
)  
          LANGUAGE SQLSCRIPT
          SQL SECURITY INVOKER
          READS SQL DATA AS 
-- DECLARE CURSOR
BEGIN 
 DECLARE CURSOR c_cursor1 FOR 
 SELECT "Dependency", "TrueDepId", "FalseDepId", "DependencyId" 
 FROM "user"."helloWorld.data::obdd" 
 WHERE "ProfileId" = :profileId 
 ORDER BY  "TrueDepId" ASC, "FalseDepId" ASC, "Dependency" ASC;
 -- ITERATION
FOR cur_row as c_cursor1 DO
v_profileId := cur_row.ProfileId;
END FOR;
END;

Like I said, I'm using the example code from the reference but I get the error that cur_row is an invalid identifier. I was not able to figure out if i have to declare it and how.

ERROR helloWorld/models/reduce_bdd.procedure

Repository: Internal error during statement execution, please see the database error traces for additional details;error executing statement; invalid identifier: CUR_ROW: line 15 col 17 (at pos 583) at ptime/query/checker/check_id.cc:365

Hopefully someone can help me.

Thanks a lot & kind regards,

  Stefan

[former_member184768 · Accepted solution]

Hi Stefan,

I think that notation of table is a valid entry. I was not aware that such a naming convention is also used.One thing that comes to attention is, can you please put double quotes around ProfileId in cur_row.ProfileId. Since you are using mixed cases with specified case as mentioned in the double quotes, it should be used the same way.

Also as a good coding practice, I would declare v_profileId beforehand.

One more thing, can you also try changing the assignment of value using SELECT statement as

select cur_row."ProfileId" into v_profileId from DUMMY;

It has been really long since I wrote the cursor code and I am yet to try it on the recent revisions of HANA.

Regards,

Ravi

[former_member182500 · Accepted solution]

Apologies for dragging an old thread up, but I was also getting exactly the same issue as Stefan (on an AWS Developer SPS07 release) regarding "invalid identifier" and wanted to add confirmation of a solution as hinted at by Ravi:

The particular line at issue, for me, was assignment of a cur_row column value where the column may be camel case.  So the following fails activation:

     v_value := cur_row.columnIdentifierCamelCase

And I cannot place in quotes, fails activation:

     v_value := cur_row."columnIdentifierCamelCase"

However following dummy select activates, note quotes:

     select cur_row."columnIdentifierCamelCase" into v_value from DUMMY;

The example on page 67 of the SPS07 SQL Script reference shows lowercase reference of column but this just did not work for me, giving the aforementioned invalid identifier error.

[ghanshyam_prasadarvind · Accepted solution]

I had the same issue. Putting column name in double quotes fixed the issue for me. I am using SP07.

[Former Member · Accepted solution]

Thanks Jon-Paul, only below one work:

select cur_row."columnIdentifierCamelCase" into v_value from DUMMY;

[aadityanigam]

Hello Stefan

I believe you mean this code

DECLARE v_isbn VARCHAR(20);

--missing code in the example

DECLARE v_title VARCHAR(20);

DECLARE v_price VARCHAR(20);

DECLARE v_crcy VARCHAR(20);

DECLARE CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR SELECT isbn, title, price, crcy

FROM books

WHERE isbn = :v_isbn ORDER BY isbn;

....

Should'nt you be passing a parameter to the cursor?

The documentation is incorrect in using the same variable name ..... its just confusing

the v_isbn in the cursor and v_isbn in the Scalar variable declaration are different.

you will see the Scalar variable used later as

FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;

Incidentally you need to declare v_title, v_price, v_crcy too just like v_isbn is which is missing too!!

Warm regards

aadi

[former_member184768]

Hi Stefan,

Couple of observations:

1) Why are you creating the procedure in _SYS_BIC schema. This schema is meant to have the runtime objects and not the user development objects like procedures.

2) What kind of data source is "user"."helloWorld.data::obdd". The cursor definition expects a SELECT statement which selects the data from a table. Can you please confirm if the above mentioned data source is a table.

Also I could not locate the exact code in SQL reference guide. Can you please point out the page number.

Regards,

Ravi

[Former Member]

Hi Ravi, thanks for your reply.

1) I'm just practicing and my procedure was created there on default.

2) Yes, you are right "user"."helloWorld.data::obdd"  is a table

The code is from page 65 of http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

I will try your other suggestions tomorrow.

Thanks for your reply