RE: SQL failed fetch in RPG

[darren@xxxxxxxxx]

Hey Mangavalli, excellent tip.  I figured out the indicators after
e-mailing and wasn't looking forward to checking those after every fetch,
but building the IFNULL into the select statement make the solution
seamless.  Thanks a bunch.





                    "Mangavalli,
                    Ramanujam"              To:     "'rpg400-l@midrange.com'"
                    <RamM@Mvmills.com        <rpg400-l@midrange.com>
                    >                       cc:
                    Sent by:                Fax to:
                    rpg400-l-admin@mi       Subject:     RE: SQL failed fetch 
in RPG
                    drange.com


                    11/15/2001 01:23
                    PM
                    Please respond to
                    rpg400-l






Darren, the SQL0305 message is "Null Indicator required" message.
Because of the LEFT OUTER JOIN, the columns from the secondary table can be
null. You can try using the IFNULL function of the columns that you are
retrieving from the second table.

Here is an example:
SELECT TABLEA.COLUMNA, IFNULL(TABLEB.COLUMNA, ' ') AS TableBColumnA,
ifnull(tableb.columnb,0) as tablebcolumnb, ifnull(tableb.columnc,
'0001-01-01') as tablebcolumnc
from tablea left outer join tableb on tablea.columna = tableb.columna

Or you can define a null indicator variable in your RPG program and use it
in the following manner:
 C/EXEC SQL
     C+  FETCH CURS1 INTO
     C+    :EMDUSR :N1EMDUSER, :EMDTFC :n1EMDTFC, :EMDSEQ, :EMDCLS,
:EMANAM,
:EMAADR
     C/END-EXEC

n1emduser and n1emdtfc are the null indicator variables. If field EMDUSR is
null, field N1EMDUSER will contain -1. If not it will contain 0. Notice the
absence of , between :EMDUSR and :n1emduser.

HTH,
Ram.
-----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.
_______________________________________________
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.