SQLScript Cursor not working

[Former Member]

Hi,

Im using cursor as below code but it is not recognizing current row identifier. Could anybody please take a look.

CREATE PROCEDURE GET_SO_BO(IN KUNNR NVARCHAR(10))
	AS BEGIN
	DECLARE V_NETWR DECIMAL(15,2);
	BEGIN
	DECLARE CURSOR C1 FOR 
	SELECT VBELN NETWR FROM "CLF01"."VBAK" WHERE KUNNR = '0000002401';
		FOR cur_row AS C1
		DO
		CALL ins_msg_proc('DOCUMENT is: ' || cur_row.VBELN );
		END FOR;
		END;
		END;

[pfefferf]

From a syntax perspective the code looks ok (of course there are not necessary statements, but that does not matter).

Do you get any error? Did you check that the table contains a data record matching the condition you defined?

[VolkerBarth]

Note, that primary tag deals with "SAP SQL Anywhere", one of SAP's database manangement systems. I'm quite sure your question relates to another DBMS (HANA?), so I would suggest to modify the used tag...

[Former Member]

i guess i had issue with version. im in higher version now and it looks ok. Thanks for your time !

[Former Member]

Hello Volker,

im quite new to this interface and even i was little surprised for same. I didn't find any better tag for this. Will try to take care of this next time

[kakshat]

Hi Sudhanshu,

I see two BEGINs in your code. Is that correct?

I know it should work without but just for kicks, did you try to see what happens if you declare cur_row explicitly on the top?

Regards,
Akshat

[Former Member]

Yes, two Begins are correct. In-fact reason for putting another Begin is to have cur_row at top.

[rindia]

Hi Sudhanshu,

Have you created the procedure ins_msg_proc.

If not then follow these steps:

1. CREATE TABLE message_box (p_msg VARCHAR(200), tstamp TIMESTAMP);

2. CREATE PROCEDURE ins_msg_proc (p_msg VARCHAR(200)) LANGUAGE SQLSCRIPT AS BEGIN INSERT INTO message_box VALUES (:p_msg, CURRENT_TIMESTAMP); END;

Then create your procedure:

CREATEPROCEDURE GET_SO_BO(IN KUNNR NVARCHAR(10))ASBEGIN
	DECLARE V_NETWR DECIMAL(15,2);BEGIN
	DECLARE CURSOR C1 FORSELECT VBELN NETWR FROM"CLF01"."VBAK" WHERE KUNNR = '0000002401';FOR cur_row AS C1
		DOCALL ins_msg_proc('DOCUMENT is: '|| cur_row.VBELN );ENDFOR;END;END;

You can find the results in the table message_box

SELECT * FROM message_box;

Regards

Raj