RE: SQL - SQL0305 Indicator variable required.

It sounds like the sum may happpen on an empty directory resulting in an
unknown value which SQL would treat as a null. If this is the case, you can
change your select to "Select coalesce(sum(ifslsiz),0) into :Dirsize" and
the problem should go away without the need to declare a host variable to
handle the null.

Gord Royle

-----Original Message-----
From: rob@dekko.com [mailto:rob@dekko.com]
Sent: Wednesday, July 03, 2002 6:12 PM
To: rpg400-l@midrange.com
Subject: SQL - SQL0305 Indicator variable required.


This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I have a file with a listing of all directory entries.  It has 3 fields
for directory, object and size.  I am trying to summarize that into a file
to total it by directory.  The summary file will have directory, size and
rundate.  The line in the following which is failing is the following:
     C/EXEC SQL
     C+ Select sum(ifslsiz) into :Dirsize
     C+ From ifslist
     C+ Where ifsldir like :Directory
     C/END-EXEC
This generates the following message in the joblog:  SQL0305-Indicator
variable required.  Frankly, I don't believe that I should be getting null
variables.  If I am doing this right it should be a guaranteed hit.
Complete source is as follows:
      /DEFINE HSpec
      /INCLUDE ROUTINES/QRPGLESRC,HSPEC
      /UNDEFINE HSpec



***************************************************************************
      * Program:      *
      *      *
      *      *
      * Modification log:      *
      * mm/dd/02 by R.Berendt, CCP    Group Dekko Services, LLC      *
      *          Created.      *
      *      *
      * Compilation instructions:      *
      *   (no special instructions this time.)      *
      *      *

***************************************************************************


      /DEFINE DSpec
      /INCLUDE ROUTINES/QRPGLESRC,SRVPGMCPY
     D dirlocal      e ds                  extname(dirlocal)
      /UNDEFINE DSpec

     C/EXEC SQL
     C+ Delete from dirlocal
     C+ Where rundate=current date
     C/END-EXEC

     C/EXEC SQL
     C+ Insert into dirlocal
     C+ (SELECT IFSLDIR,0,CURRENT DATE
     C+  FROM IFSLIST
     C+  WHERE IFSLDIR NOT LIKE '/%/%'
     C+  GROUP BY IFSLDIR)
     C/END-EXEC
      * at this point dirlocal has 73 records.
     C/EXEC SQL
     C+ Declare C1 cursor for
     C+ Select Directory
     C+ From dirlocal
     C/END-EXEC

     C/EXEC SQL
     C+ Open C1
     C/END-EXEC

     C/EXEC SQL
     C+ Fetch C1 into :Directory
     C/END-EXEC

      /free
       dow sqlcod=0;
        Directory=%trim(Directory) + '%';
        Dirsize=*zeros;
      /end-free

     C/EXEC SQL
     C+ Select sum(ifslsiz) into :Dirsize
     C+ From ifslist
     C+ Where ifsldir like :Directory
     C/END-EXEC
      * the above generated the SQL0305
      * Is it that I am using a sum into a host variable
      * defining my LIKE wrong
      * or what?

     C/EXEC SQL
     C+ Update dirlocal
     C+ Set dirsize=:dirsize
     C+ Where current of C1
     C/END-EXEC

     C/EXEC SQL
     C+ Fetch C1 into :Directory
     C/END-EXEC

      /free
       EndDo;
      /end-free

     C/EXEC SQL
     C+ Close C1
     C/END-EXEC

      /free
       *inlr=*on;
       return;
      /end-free

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin
_______________________________________________
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.