Skip to Content
avatar image
Former Member

cursor statement in HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 16, 2012 at 08:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Oct 16, 2012 at 08:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded