|
midrange.com
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
