cursor statement in HANA

[martin_lindner]

Hi experts,

I want to call two times a procedure from me and merge the results via union in an loop. So I searched for the access of the resultset and found the CURSOR statement with access via the for loop. The example in the SPS 4 Guide is that one:

CREATE PROCEDURE foreach_proc() LANGUAGE SQLSCRIPT AS

     v_isbn VARCHAR(20) := '';

     CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR SELECT isbn, title, price, crcy FROM books ORDER BY isbn;

     BEGIN

          FOR cur_row as c_cursor1 DO

               CALL ins_msg_proc('book title is: ' || cur_row.title);

          END FOR;

     END;

I know that the example has a misstake, because they declared the cursor c_curor1 with an import parameter that has to be filled, so the FOR-statement should start with FOR cur_row AS c_cursor1(:v_isbn) DO... .

But that isn't my problem. My statement looks so:

FOR iterator_row AS entity_cursor(:iv_link_type_id, :iv_source_mandt, iv_source_guid_1, iv_source_guid_2, iv_source_id_1, iv_source_id_2, iv_source_type_id) DO

   CALL "_SYS_BIC"."d052319/PROC_FETCH_ENTITY"(  :iterator_row.source_mandt, :iterator_row.source_guid_1, :iterator_row.source_guid_2,

                                                                                    :iterator_row.source_id_1, :iterator_row.source_id_2, :iterator_row.source_type_id,

                                                                                     lt_details_1);

   CALL "_SYS_BIC"."d052319/PROC_FETCH_ENTITY"( iterator_row.destination_mandt, iterator_row.DESTINATION_GUID_1,

                                                                                   iterator_row.DESTINATION_GUID_2, iterator_row.DESTINATION_ID_1,          

                                                                                   iterator_row.DESTINATION_ID_2, iterator_row.DESTINATION_TYPE_ID,

                                                                                   lt_details_2);

   lt_details = CE_UNION_ALL(:lt_details_1,:lt_details_2);

   ot_details = CE_UNION_ALL(:lt_details,:ot_details);

END FOR;

You can see I try it in different ways to access my iterator_row. In the first call with the : before iterator_row because I've thinked is access to an reference so I need this syntax. The second call is without.

But in both cases my activation failed.

For the first case it says:   for oid {tenant: , package: d052319, name: GET_ENTITY_INFO, suffix: procedure}: identifier must be declared: ITERATOR_ROW: line 39 col 49 at ptime/query/sqlscript2/so_so2l.cc:7082.

The second run without the : before iterator_row the error message is:

for oid {tenant: , package: d052319, name: GET_ENTITY_INFO, suffix: procedure}: identifier must be declared: DESTINATION_MANDT at ptime/query/sqlscript2/so_routine.cc:175.

My cursor that I want to use is:

CURSOR entity_cursor ( LINK_TYPE_ID   VARCHAR (255),

      SOURCE_MANDT    VARCHAR (3),

        SOURCE_GUID_1    VARCHAR (32),

       SOURCE_GUID_2    VARCHAR (32),

       SOURCE_ID_1    VARCHAR (40),

       SOURCE_ID_2    VARCHAR (40),

       SOURCE_TYPE_ID    VARCHAR (4)

      )

FOR SELECT  "SOURCE_MANDT","SOURCE_GUID_1","SOURCE_GUID_2","SOURCE_ID_1","SOURCE_ID_2","SOURCE_TYPE_ID",

       "DESTINATION_MANDT","DESTINATION_GUID_1","DESTINATION_GUID_2","DESTINATION_ID_1","DESTINATION_ID_2","DESTINATION_TYPE_ID"

     FROM "D052319"."ZTN_ENTITY_LINK"

         WHERE LINK_TYPE_ID   = :link_type_id

         and   SOURCE_MANDT   = :source_mandt

         and   SOURCE_GUID_1   = :source_guid_1

         and   SOURCE_GUID_2   = :source_guid_2

         and   SOURCE_ID_1        = :source_ID_1

         and   SOURCE_ID_2        = :source_ID_2

         and   SOURCE_TYPE_ID = :source_type_id;

Does anybody knows how to use the CURSOR statement in a correct way or has an other solution to solve my problem described above?

Best regards,

Martin

[former_member184768 · Accepted solution]

Hi Martin,

Please find the code below which I could use successfully:

   CURSOR c_sosys (vc_zsosy_fr NVARCHAR(5), vc_zsosy_to NVARCHAR(5))

   FOR

     select distinct "ZSOSY" as ZSOSY

     from sapuser."QZGCODE"

     where "ZSOSY" between :vc_zsosy_fr and :vc_zsosy_to;

   v_source_system NVARCHAR  (5) := '';

BEGIN

   FOR sosys_row AS c_sosys(:v_zsosy_fr, :v_zsosy_to) DO

     select sosys_row.ZSOSY into v_source_system from DUMMY;

       v_l_val2 := 'Source System : '||:v_source_system;

            UPSERT Z_TABLE ...

             where oh."ZSOSY" = sosys_row.ZSOSY

             CALL PROC_LOG_MSG (:v_l_val2);

   END FOR; --- End of Cursor loop c_sosys

END;

Regards,

Ravi

[martin_lindner · Accepted solution]

Hi Ravi,

thanks for your fast reply. I also find out now that it is not possible to call procedures directly with the result line. I had to put it into a local variable before i use it in the call.

So if I did it that way it works:

....

lv_mandt   VARCHAR(3);

lv_guid_1   VARCHAR(32);

lv_guid_2  VARCHAR(32);

lv_id_1   VARCHAR(40);

lv_id_2   VARCHAR(40);

lv_type   VARCHAR(4);

....

BEGIN

  ....

   lv_mandt  := iterator_row.source_mandt;

   lv_guid_1 := iterator_row.source_guid_1;

   lv_guid_2 := iterator_row.source_guid_2;

   lv_id_1   := iterator_row.source_id_1;

   lv_id_2   := iterator_row.source_id_2;

   lv_type   := iterator_row.source_type_id;

   CALL "_SYS_BIC"."d052319/PROC_FETCH_ENTITY"( :lv_mandt, :lv_guid_1, :lv_guid_2, :lv_id_1, :lv_id_2, :lv_type, lt_details_1);

....

Many thanks to you Ravi ,

Martin

[former_member184768]

Looking at your code, can you please try to use alias and use the exact name (using the same case) for the iterator_row.<column>.

Just a thought.

Regards,

Ravi