AW: SQL fetch multiple records and SQLER3

Hi Mike,

on which release are you?

I suspect, that SQLER3 only counts the number of rows where records in both
tables are found.
Normally SQL does not count NULL-Values. Nevertheless, I think the total of
rows returned must returned in SQLER3.
I didn't found any note that there must be an exception when NULL values are
returned.
A work around may be to use the SCALAR Function COALESCE() to set a default
value if NULL is returned.

Check I you are on the current PTF-Level and check for all PTFs about
database or SQL.

If you are already on release V5R3M0 or higher, you also might use the GET
DIAGNOSTICS-Statement to retrieve the appropriate information.

C/EXEC SQL   GET DIAGNOSTICS :RowReturned = ROW_COUNT
C/END-EXEC

C/EXEC SQL   GET DIAGNOSTICS :LastRow = DB2_LAST_ROW
C/END-EXEC

ROW_COUNT:
Identifies the number of rows associated with the previous SQL statement
that was executed. If the previous SQL statement is a multiple-row-fetch,
ROW_COUNT identifies the number of rows fetched. Otherwise, the value zero
is returned.
But I assume that ROW_COUNT is based on the same calculation as the value
returned in SQLER3

DB2_LAST_ROW:
For a multiple-row-fetch statement, a value of +100 may be returned if the
set of rows that have been fetched contains the last row currently in the
table for cursors that are fetching forward, or contains the first row
currently in the table for cursors that are fetching backward.

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx]Im Auftrag
von rick baird
Gesendet: Samstag, 1. April 2006 00:08
An: RPG programming on the AS400 / iSeries
Betreff: Re: SQL fetch multiple records and SQLER3


Mike,

no, I'm not clearing, but looking at the data structure, it's clearly
fetching 13 records - it's the first fetch of the program and the
entire data structure is filled.

The records in the data structure are exactly what I expect them to be
- but SQLER3 only says I fetched 7, when clearly I fetched 13 rows.

If I were fetching one row at a time, I would use SQLCOD instead, but
when fetching multiple rows, I've always found SQLER3 to be reliable -
if less than the number of rows requested, i'm at eof.   even if I
used SQLCOD instead, I would need to know how many rows I fetched (if
less than 13) were fetched on the last fetch.

Really, the more I look at this, the more I feel it must be a bug.

Rick

On 3/31/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx>
wrote:
> We've always used SQLCOD for our conditioning.  If < 0 or = 100, then no
> more records were fetched.
>
> Anyway,  Are you clearing all the INTO fields before fetching them.  The
> fetch will not clear those fields if records were not fetched.
>
> Michael Schutte
> Work 614-492-7419
> email  michael_schutte@xxxxxxxxxxxx
>
>
>
>             "rick baird"
>             <rick.baird@gmail
>             .com>                                                      To
>             Sent by:                  "RPG programming on the AS400 /
>             rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>
>             midrange.com                                               cc
>
>                                                                   Subject
>             03/31/2006 04:46          SQL fetch multiple records and
>             PM                        SQLER3
>
>
>             Please respond to
>              RPG programming
>              on the AS400 /
>                  iSeries
>             <rpg400-l@midrang
>                  e.com>
>
>
>
>
>
>
> Hey all,
>
> I'm doing an SQLRPGLE select - left outer join on two files and
> fetching 13 records at a time to fill a subfile page.
>
> the join has the possibility to have left side records without right
> side records, and I'm taking that into account ( - alwnull(*usrctl)
> and checking the null indicators of records fetched ).
>
> my problem is that I need to know how many records I fetched so I know
> when i'm at EOF.
>
> I've always used SQLER3 to tell me this, but it's not working now.
>
> The first fetch I do, my data structure has 13 records in it, and they
> are the records I expected to get, but SQLER3 has 7 in it.
>
> coincidently, record 8 is the first record with nulls in the right
> side file fields.
>
> what gives?  It fetched 13 records, but SQLER3 tells me only 7 were
> fetched.
>
> Any help would be appreciated.
>
> Rick
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.