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