RE: SQL Problem

[Gord Royle <GRoyle@xxxxxxxx>]

I've been away from my E-Mail for a couple of days, but I read all (I think)
of the responses to your question. I think that I can add a little. Null
WILL be returned in a left outer join if 1) the field is null capable and is
null or 2) if no row is found. There were all kinds of excellent suggestions
about how to deal with nulls in your code. The easiest, as long as you don't
care, is "coalesce" and I didn't see it mentioned. What "coalesce" does
simply is substitute a value for null. So your select of "b.itcls" would
read "coalesce(b.itcls,0)" if it's Numeric or "coalesce(b.itcls,'Some Text')
if it's Char. This will ensure that no nulls are returned.

-----Original Message-----
From: MWalter@hanoverwire.com [mailto:MWalter@hanoverwire.com]
Sent: Thursday, September 13, 2001 4:40 PM
To: rpg400-l@midrange.com
Subject: SQL Problem


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.


Cott - The Leader in Premium Retailer Brand Beverage Innovation.