Re: SQL Problem

["Alexei Pytel" <pytel@xxxxxxxxxx>]

Most probably you have null-capable field which has a null value.
To fetch value for such field you have to supply null value indicator field
in a FETCH statement - by definition null value cannot be returned in a
field itself.

If you do not want a null indicator you can use a CASE clause to convert
null value to some special value in a field, or use an approriate SQL
function if you are on a release which does not support CASE.

    Alexei Pytel

"No one can beat unbeatable"





                    MWalter@hanoverwi
                    re.com                  To:     rpg400-l@midrange.com
                    Sent by:                cc:
                    rpg400-l-admin@mi       Subject:     SQL Problem
                    drange.com


                    09/13/2001 03:39
                    PM
                    Please respond to
                    rpg400-l





Well, I had to 'override' the SQL preprocessor's code. I don't know what is
gonna happen down the road but I they powers that be are chomping at the
bit for this code.

Here is the significant code:

     c                   eval      statement =
     c                              'SELECT a.itnbr, b.itdsc, a.edate,
tdate,' +
     c                              ' a.itcls, a.ituom, a.price, b.itcls '
+
     c                              'FROM Cntmst a LEFT OUTER JOIN Itemasa
b ' +
     c                              'ON a.itnbr = b.itnbr +
     c                              WHERE cont# = ''' + %trim(cont#) + '''
+
     c                              ORDER BY ' + sortField

      * SQL to prepare statement
     C/exec sql
     C+ prepare stmt from :statement
     C/end-exec


     C/exec sql
     C+ declare C1 cursor for stmt
     C/end-exec

     C/exec sql
     C+ open C1
     C/end-exec

     C                   ENDSR

      * Fetch the records from the cursor
     C     @fetch_record BEGSR

     C/exec sql
     C+ fetch C1 into :sitnbr, :xxxx,  :date8, :date82,
     c+ :sitcls, :situom, :sprice
     C/end-exec

      * end of file
     c                   if        (sqlstt = '02000' or sqlcod < 0) and
     c                             sqlcod <> -305
     c                   eval      sfl_end = *on
     c                   endif

     c                   if        sqlcod = -305
     c                   eval      sitnbr = sql_00011
     c                   eval      xxxx = sql_00012
     c                   eval      date8 = sql_00013
     c                   eval      date82 = sql_00014
     c                   eval      sitcls = sql_00015
     c                   eval      situom = sql_00016
     c                   eval      sprice = sql_00017
     c                   endif

     C                   ENDSR


If anyone has any information on why I'm receiving a sqlcod of -305
(Indicator variable required) on a LEFT OUTER JOIN when there are
non-matching primary records with the secondary record, I'd appreciate
being left in on it. There aren't any indicator variables in the table. I
didn't even think you could put indicator variables in a PF. At least using
DDS.

Thanks,

Mark


Mark Walter
Sr. Programmer/Analyst
Hanover Wire Cloth a div of CCX, Inc.
mwalter@hanoverwire.com
http://www.hanoverwire.com
717.637.3795 Ext.3040

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