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



This is true - sometimes....

if you doing a left outer join, SQLER3 is unreliable.

If you fetch 10 rows, and the 4th row fetched is an unmatched row with
the right half of the join, then SQLER3 = 3, even though you actually
recieved 10 rows from the left side of the join.

that's been my experience anyway.

On 8/17/06, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:
Hi,

SQLER3 represents the number of rows fetched and not the number of total
rows in the result set.
You only fetched a single row, that's why SQLER3 is one.
When using a multiple row fetch into either a multi occurence data structure
or an array data structure, SQLER3 returns the number or row fetched with a
single fetch. To check if you fetched all rows including the last one, you
have to check SQLER5. SQLER5 returns 100 if the last row was fetched with
the multiple row fetch.

   MaxNbrOfRows = %Elem(MyArrayDS);
 /End-Free
C/EXEC SQL  Fetch From CsrC1 For :MaxNbrOfRows Rows
C+                into :MyArrayDS
C/END-EXEC
 /Free
  If SQLER3 = *Zeros  // *Zero Rows are Fetched
  If SQLER5 = 100     // The last row of the result set was fetched.

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 Charles St-Laurent
Gesendet: Donnerstag, 17. August 2006 16:23
An: rpg400-l@xxxxxxxxxxxx
Betreff: Get the row count from the SQLCA


Hi!

I try to get the row count from the SQLCA in a SQLRPGLE program in which I
build my Select statement dynamically in a string. The program works fine
but I want to get the row count from the SQLCA before trying to fill my
subfile with my Select result.

I know that row count is usually in SQLER3 (I think so) but when I try to
get this value before my FETCH loop, the value is 1.

This is what I do:

Eval SQLSTR = 'SELECT MY_DATA FROM MY_TABLE'
C/EXEC SQL PREPARE S1 FROM :SQLSTR
C/END-EXEC
C/EXEC SQL DECLARE C1 CURSOR FOR S1
C/END-EXEC
C/EXEC SQL OPEN C1
C/END-EXEC
C/EXEC SQL FETCH C1 INTO :SQLREC
C/END-EXEC
C                   DUMP

When I look at the result of the DUMP operation, SQLER3 = 1 but in fact the
result should be 2... why?

Thanks for your help
Charles



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