× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Gord,

I fixed the problem with the Null Indicator but your method seems allot
more simple. I'll keep it in mind


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



                    Gord Royle
                    <GRoyle@cott.com>        To:     "'rpg400-l@midrange.com'" 
<rpg400-l@midrange.com>
                    Sent by:                 cc:
                    rpg400-l-admin@mi        Subject:     RE: SQL Problem
                    drange.com


                    09/14/01 02:46 PM
                    Please respond to
                    rpg400-l






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.


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







As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.