Skip to Content
1

SQLScript Cursor not working

Jan 18, 2017 at 11:54 AM

128

avatar image

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;

10 |10000 characters needed characters left characters exceeded

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?

1

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

0

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...

0

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

1
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Raj K
Feb 02, 2017 at 04:34 AM
-1

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

Share
10 |10000 characters needed characters left characters exceeded
Kumar Akshat
Feb 02, 2017 at 06:47 AM
-1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0