How can i control the number of rows Fetched?

[karu76]

Hi All,

I am aware that i can retrieve the number of rows i need by using the "fetch first N rows" statement.

Can anyone suggest a way to retrieve "N" number of rows where "N" is dynamic.

I am passing in the "N" value as a parameter to the SP.

Any other suggestions to achieve this is also appreciated. I m using DB2 version 8.

Rgds.

L.Karu

 

[MarcLodge]

If you are passing the parameter to a stored procedure, can the SP not perform the required function programatically (presuming that the SP is writtin in a language like Cobol etc.)

Marc

 

[karu76]

Hi Marc,

Thanku for reverting .. i m actually new to DB2. I have attached the SQL below maybe u can tell me how i can achieve it.

I m using SQL to create my stored procedure.. it goes like this..

CREATE PROCEDURE IRND.PTY
(
IN ID_INT DECIMAL(13,0),
IN QT_MAX INTEGER,
)

RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

-- Declare cursor for datatables
DECLARE sQuery VARCHAR(32000);

DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT T_R.ID_INT, T_R.ID_REL_INT
FROM IRND.T_RELATION AS T_R
WHERE (T_R.ID_INT = PTY.ID_INT)
with ur fetch first 4 rows only;

OPEN cursor1;

END P1
========================================
HOW DO SET QT_MAX inplace of 4.
========================================

 

[MarcLodge]

Hi Karu,
I think you may need to look at what you are trying to achieve, and then look at the best ways to do it. I don't know what environments or languages you are working with, but it seems to me that you might be approaching this from the wrong angle.

The stored procedure that you have shown us, only opens a cursor; no fetch of the data is made, and no ORDER BY or WHERE clauses have been coded.

An SP is often coded in a language other than raw SQL, so that it can provide functionality that is not easily obtainable in SQL.

Have a think about what you are trying to do, and see if it can be executed in whatever language/environment you are attempting to call the SP from.

If you are still stuck, get back to us with the langauge/environment you are working with, and the objectives of the problem.

Hope this helps

Marc

 

[karu76]

Hi mark,

I agree on what you have suggested. But this is how the SPs are implemented here. All we do is open the cursor n there is an "agent" which does the fetch and stuff like that. By the way i m working on .Net and BD2.

I have actually simpilified the query as the rest of the information is lengthy n redundant.

ok.. my problem is i want to make this simple query dynamic. My requirement currently is to retrieve the 1st 4 rows from that table. Without this restriction it may retrieve thousands of rows.

This "4 rows" condition may change in the future. So to avoid changing at too many places i have declared a CONSTANT and wish to pass this as a parameter to this query.

Hope my reasoning to implement this is valid ..haha..

thk u.

L.Karu

 

[MarcLodge]

L.Karu,

How do you get the passed parameter into the SQL? Or is that your problem?

Marc

 

[BrianTyler]

You could use the OLAP functions to create logical row numbers.

Find the details by reading the SQL cookbook - search the net for 'Graeme Birchall' and you'll find a link.

 

[cdlvj]

In the create procedure, is there not a keyword to limit the number of rows returned? Having worked on ver 6, there was one I think, but it did not work. I used spufi a lot, and they had limits set via a parm. It is a parm similar to the Result sets 1.

 

[karu76]

Hi,

I am looking for something that works like bind variables function in oracle.

eg)

CREATE PROCEDURE IRND.PTY
(
IN ID_INT DECIMAL(13,0),
IN QT_MAX INTEGER,
)

RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

-- Declare cursor for datatables
DECLARE sQuery VARCHAR(32000);

DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT T_R.ID_INT, T_R.ID_REL_INT
FROM IRND.T_RELATION AS T_R
WHERE (T_R.ID_INT = PTY.ID_INT)
with ur fetch first :QT_MAX rows only; <-- This line.

OPEN cursor1;

END P1

Maybe u guys can suggest how u would handle such a requirement.

Thank you all for your continued assistance.

L.Karu

 

[MarcLodge]

L.Karu,
In the statement FETCH FIRST 10 ROWS ONLY, you are able to replace the number 10 with a variable. The problem that you have is environmental, in that you need to know how to pass a variable to your stored procedures and how your stored procedures will use that variable. From what you have already described, you have an 'agent' that performs the fetches, and this makes it harder for us to fully understand what is going on at your site.

I would suggest asking someone at your place of work how to pass a variable to an SP and get it to use it. I'm sure this must have already been done, as I think it unlikely that all your SPs have been set up with no WHERE clauses where the value is passed. Although they may have been!

For reference you might wish to look at this great Craig Mullins article

http://www.craigsmullins.com/dbu_0502.htm

which offers another way of getting the first 10 rows of a table, but I think your problem lies in finding out how your site is set up.

Hope this is of some help, and please get back to me if I've misunderstood what you are after.

Regards,
Marc

 

[karu76]

Hi Marc,

U have read me right. Let me ask around. If i do get a valuable input will post it here.

Thk u for your references and time

L.Karu

 

[CyberFan]

You could use EXECUTE IMMEDIATE.