RE: SQL Problem

Mark,

If you use a left outer join, like in say "TABLEA  LEFT OUTER JOIN TABLEB
ON TABLEA.COLUMNA = TABLEB.COLUMNB",
Nulls will be returned for all columns in TABLEB if there are no matching
records in TABLEB.

I would do as follows:

D w@Nullind1              S               4B 0
D w@Nullind2              S               4B 0

Use this variable in the FETCH statement as follows:
C/exec sql
C+ fetch C1 into :sitnbr , :xxxx :w@NullInd1,  :date8, :date82,
c+ :sitcls, :situom, :sprice :w@NullInd2
C/end-exec

Notice the absence of commas between :xxxx and :w@NullInd1 and between
:sprice and :w@NullInd2.

This will place a -1 in w@NullInd1 if the value in XXXX is null.
You can use the value in w@NullInd1 in your program and that should take
care of it.

In general, for a null capable field if you use
:HostVariable :NullIndicator ,

:NullIndicator will have -1 if Host variable is null.


As an alternative, you can also use the following:
     c                   eval      statement =
     c                              'SELECT a.itnbr, IFNULL(b.itdsc, '' ''),
a.edate,
tdate,' +
     c                              ' a.itcls, a.ituom, a.price,
IFNULL(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
If the value of b.itdsc is null, you will get a blank into your host
variable. Same goes for b.itcls.



HTH.


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