RE: SQL Problem

["Phil" <sublime78ska@xxxxxxxxx>]

Mark,

Unmatched records probably result in null values in the fields you are
inserting into from the joined file.  You can define null indicator
variables as 5i 0 fields to handle whether the value is null.  The number of
these indicator variables needs to match the number of fields you are
inserting into.  It would look like this:

     C/exec sql
     C+ fetch C1 into :sitnbr :field1, :xxxx :field2,  :date8 :field3,
:date82 :field4,
     c+ :sitcls :field5, :situom :field6, :sprice :field7
     C/end-exec

**Notice no comma between the host variable and the indicator variable**

If a null is returned, as from an unmatched record on a join, then a
negative value will be placed in the indicator variables.

You could also do this:

D  hostDS    ds
D     sitnbr      1
D     xxxx        5
--etc--
D  nullDS    ds
D     field1      5i 0
D     field2      5i 0
--etc--

C/exec sql
C+ fetch c1 into :hostDS :nullDS
C/end-exec

Phil


> -----Original Message-----
> From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
> Behalf Of 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.
>


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com