Is it possible to code "FETCH FIRST <WS-ROWS> ROW

ravi1 · New User Joined: 27 Apr 2006 Posts: 11

Hi,

Can anyone suggest me whether is it possible to code like

In the last line declaration of Cursor,

FETCH FIRST <WS-ROWS> ROWS ONLY?

where WS-ROWS is declared as PIC s9(4) comp.

Thanks in Advance,

guptae · Posted: Tue Dec 12, 2006 5:38 pm

Hi There,

Yes u can declare it but question is why u want to use it.

ravi1 · New User Joined: 27 Apr 2006 Posts: 11

Hi,

Thanks for your response. This requirement is for Stored Procedure.
User is going to enter required no. of rows from the web.

But I am getting error in DB2 precompilation saying that

WS-ROWS is llegal keyword.

Can u tell me how to solve it?

thanks.

guptae · Posted: Tue Dec 12, 2006 5:57 pm

Hey Ravi,

Just try to use WS-ROWS as host variable i mean by

dharmendra_kp · New User Joined: 10 Mar 2005 Posts: 33 Location: Lewiston

Also please keep in mind that it is DB2 version dependent.
I mean FETCH FIRST clause is available on V7 onward.

ravi1 · New User Joined: 27 Apr 2006 Posts: 11

It is accepting hard coded values

for ex.

FIRST FIRST 1000 ROWS ONLY.

But it is saying illegal use of keyword if I declare

WS-ROWS as PIC S9(4) COMP.

FETCH FIRST <WS-ROWS> ROWS ONLY.

thanks.

Rajen · New User Joined: 05 May 2005 Posts: 61

Hi Ravi,
Can you please try adding OPTIMIZE clause at the end of query.

I guess you can try with below mentioned query.

FETCH FIRST <WS-ROWS> ROWS ONLY.

OPTIMIZE FOR <WS-OPTIMIZE-ROWS> ROWS;

Here WS-OPTIMIZE-ROWS is greater than WS-ROWS.

Can you please try with this query and let us know the results.
I dont know if this is the right solution or not but definitely want to give a try.

Thanks,
Rajen.