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



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.







As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.