RE: SQL failed fetch in RPG

[Gord Royle <GRoyle@xxxxxxxx>]

Try this:

    C/EXEC SQL
     C+  DECLARE CURS1 CURSOR FOR
     C+    SELECT EMDUSR, EMDTFC,
     C+      EMDSEQ, EMDCLS, COALESCE(EMANAM,'UNKNOWN'), COALESCE(EMAADR,'
')
     C+    FROM EMAILFDST LEFT OUTER JOIN EMAILFADR
     C+      ON EMDUSR=EMAUSR AND
     C+        EMDSEQ=EMASEQ
     C+      WHERE EMDUSR<>'' AND
     C+         EMDFIL=:EMGGRP AND
     C+         EMDCMP=:EMGCMP AND
     C+         EMDFAC=:EMGFAC
     C+       ORDER BY EMDUSR
     C/END-EXEC

You have to "manage" the nulls. The easiest way, if you don't care about
their existence, is to use "COALESCE" on the fields that are null-capable
(in your case, any field in EMAILFADR).

Gord Royle
Cott Corporation

-----Original Message-----
From: darren@dekko.com [mailto:darren@dekko.com]
Sent: Thursday, November 15, 2001 1:16 PM
To: rpg400-l@midrange.com
Subject: SQL failed fetch in RPG



I'm fetching for a cursor declared using two files with a left outer join.
What this means is I want to see all the records in the first file and
lookup a value in the second file.  The key field that is used to lookup a
record in the second file may not exist in the second file, which should
return a null value according to IBM's SQL manual.  This works great in
interactive SQL but when fetching these records where there isn't a match
in the second file I get back an SQLCOD='-0305' which is something about a
null value without an indicator.  And it doesn't retrieve the values for
the first file so I can't ignore the error.  Any advice as to what I might
need to do here?  Hopefully the code I've pasted in here is readable by
your viewer.


     C/EXEC SQL
     C+  DECLARE CURS1 CURSOR FOR
     C+    SELECT EMDUSR, EMDTFC,
     C+      EMDSEQ, EMDCLS, EMANAM, EMAADR
     C+    FROM EMAILFDST LEFT OUTER JOIN EMAILFADR
     C+      ON EMDUSR=EMAUSR AND
     C+        EMDSEQ=EMASEQ
     C+      WHERE EMDUSR<>'' AND
     C+         EMDFIL=:EMGGRP AND
     C+         EMDCMP=:EMGCMP AND
     C+         EMDFAC=:EMGFAC
     C+       ORDER BY EMDUSR
     C/END-EXEC

....OPEN CURSOR and stuff

     C/EXEC SQL
     C+  FETCH CURS1 INTO
     C+    :EMDUSR, :EMDTFC, :EMDSEQ, :EMDCLS, :EMANAM, :EMAADR
     C/END-EXEC


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


Cott - The Leader in Premium Retailer Brand Beverage Innovation.