× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. April 2006

Re: SQL fetch multiple records and SQLER3

sorry about that!

On 4/1/06, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:
> >Brigitta,
>
> >> Hi Mike,
>
> >It's Rick, but that's ok. ;)
>
> ... and it's Birgitta, not Brigitta, so we are quits ;)
>
> Mit freundlichen Gru?en / 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@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von rick baird
> Gesendet: Samstag, 1. April 2006 16:30
> An: RPG programming on the AS400 / iSeries
> Betreff: Re: SQL fetch multiple records and SQLER3
>
>
> 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.
> >
> >
>
> --
> 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.
>
>

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.