Find the number of records fetched in a cursor.
-
- New Member
- Posts: 6
- Joined: Fri Nov 21, 2014 5:34 pm
Find the number of records fetched in a cursor.
Hi
While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.
While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.
-
- Global Moderator
- Posts: 588
- Joined: Wed Nov 20, 2013 11:53 am
- Location: Mars
Re: Find the number of records fetched in a cursor.
If you could elaborate you could get better reply
I would normally do a Count(*)
I would normally do a Count(*)
zprogrammer
-
- Global Moderator
- Posts: 826
- Joined: Wed Sep 11, 2013 3:57 pm
Re: Find the number of records fetched in a cursor.
IIRC the SQLERRD(3) variable in the SQLCA should contain the value You are looking for
https://www-01.ibm.com/support/knowledg ... ields.html
https://www-01.ibm.com/support/knowledg ... ields.html
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
-
- Website Team
- Posts: 70
- Joined: Wed Jul 31, 2013 10:19 pm
Re: Find the number of records fetched in a cursor.
I doubt SQLERRD(3) will give the count no of rows selected by a cursor before hand.
As far as selecting rows concerned, SQLERRD(3) (3) gives the count of rows selected in a multi-fetch/rowset Oriented cursor for a single fetch operation.
I don't think there is any field in SQLCA which will give the total no rows selected by a cursor in advance
Correct me if I am wrong
Thanks and regards,
Chandan
As far as selecting rows concerned, SQLERRD(3) (3) gives the count of rows selected in a multi-fetch/rowset Oriented cursor for a single fetch operation.
I don't think there is any field in SQLCA which will give the total no rows selected by a cursor in advance
Correct me if I am wrong
Thanks and regards,
Chandan
-
- New Member
- Posts: 6
- Joined: Fri Nov 21, 2014 5:34 pm
Re: Find the number of records fetched in a cursor.
Thank for the answers. So basically, it is not possible actually?
Re: Find the number of records fetched in a cursor.
Pandora-Box's reply still applies - do a COUNT using your selection criteria before doing your SELECT.
Regards
Nic
Nic
-
- New Member
- Posts: 6
- Joined: Fri Nov 21, 2014 5:34 pm
Re: Find the number of records fetched in a cursor.
Ok. But I still not sure why it is not possible.
-
- Global Moderator
- Posts: 588
- Joined: Wed Nov 20, 2013 11:53 am
- Location: Mars
Re: Find the number of records fetched in a cursor.
Suntles,
You could do a count(*) and find the number of records and then process the cursor.
You could do a count(*) and find the number of records and then process the cursor.
zprogrammer
- Anuj Dhawan
- Founder
- Posts: 2802
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Find the number of records fetched in a cursor.
Let's take a step back and understand what is the life-cycle of a CURSOR in a COBOL DB2 program. There are four stages -Suntles Singh wrote:While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.
- DECLARE
OPEN
FETCH
CLOSE
The cursor won't even have a clue about the rows in the table until you FETCH and by then two of the stages of its life-cycle are already passed! Which means you are expecting something before it happened. It's just not possible, AFAIK, in DB2-on-zOS[sup]![/sup]. Cursors do not store any rows they are a pointer to a compiled SQL statement that can be used to fetch the rows and until you FETCH, well, the count is zero. For example, you can have a CURSOR like this:
Code: Select all
SELECT column1, column2
FROM sometablename
WHERE some_condition;
Code: Select all
SELECT count(*)
INTO ctr_variable
FROM sometablename
WHERE some_condition;
[sup]![/sup]: In oracle[sup]*[/sup], you can use %ROWCOUNT like this:
Code: Select all
DECLARE
CURSOR some_cursor IS
SELECT *
FROM SomeTable;
BEGIN
OPEN some_cursor;
DBMS_OUTPUT.PUT_LINE(some_cursor%ROWCOUNT);
CLOSE some_cursor;
END;
But it'll always yield 0 for count, because no FETCH is done yet.[/size]
[sup]*[/sup]Have taken reference from oracle documents and online references.
Thanks,
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
-
- New Member
- Posts: 6
- Joined: Fri Nov 21, 2014 5:34 pm
Re: Find the number of records fetched in a cursor.
Thanks Anuj! It's a lengthy explain. But I am able to get what you all have said.. need to read it multiple times.Anuj Dhawan wrote:Let's take a step back and understand what is the life-cycle of a CURSOR in a COBOL DB2 program. There are four stages -Suntles Singh wrote:While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.
... the cursor.
- DECLARE
OPEN
FETCH
CLOSE
The cursor won't even have a clue about the rows in the table until you FETCH and by then two of the stages of its life-cycle are already passed! Which means you are expecting something before it happened. It's just not possible, AFAIK, in DB2-on-zOS[sup]![/sup]. Cursors do not store any rows they are a pointer to a compiled SQL statement that can be used to fetch the rows and until you FETCH, well, the count is zero. For example, you can have a CURSOR like this:
And can write a separate SQL with the same WHERE clause to get the count of rows, as PB and nicc has said:Code: Select all
SELECT column1, column2 FROM sometablename WHERE some_condition;
Code: Select all
SELECT count(*) INTO ctr_variable FROM sometablename WHERE some_condition;
[sup]![/sup]: In oracle[sup]*[/sup], you can use %ROWCOUNT like this:
Code: Select all
DECLARE CURSOR some_cursor IS SELECT * FROM SomeTable; BEGIN OPEN some_cursor; DBMS_OUTPUT.PUT_LINE(some_cursor%ROWCOUNT); CLOSE some_cursor; END;
But it'll always yield 0 for count, because no FETCH is done yet.[/size]
[sup]*[/sup]Have taken reference from oracle documents and online references.
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute