|
>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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
