Re: SQL fetch multiple records and SQLER3 -- RPG400-L

Brigitta,

> Hi Mike,

It's Rick, but that's ok. ;)

> on which release are you?

v5r3

> I suspect, that SQLER3 only counts the number of rows where records in both
> tables are found.

interestingly, it stopped counting at the first record with right side
nulls.  In debug, the first fetch showed 7 in SQLER3, but the data
structure showed (sort of):

left side  right side
1             1
2             2
3             3
4             4
5             5
6             6
7             7
8             -
9             -
10           10
11           11
12            -
13           13

so, even if it's only showing how many matched records it found, it's wrong.

> Normally SQL does not count NULL-Values. Nevertheless, I think the total of
> rows returned must returned in SQLER3.

I agree.   Thanks for the suggestions.  I'm on vacation till friday,
so I'll take another look then.

Thanks,

Rick

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