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



My apologies for such a short answer, but time is a valuable commodity 
today.

IFNULL() is your friend....

HTH.

L. Wayne James
Senior Developer
Agilysys, Inc.
Hospitality Solutions Group
11545 Wills Road
Alpharetta, GA 30004
Phone:  770-962-6425 x1252
Email:  Wayne.James@xxxxxxxxxxxx



"Dan Bale" <dbale@xxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/12/2004 09:09 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
Left join question (was: SQL table joins: Join vs. Where)






O.k., I've got this working now with:

SELECT
   ALL   SUBSTR(T03.SCDSSS,1,6) AS GLACCT, T01.INVNCV,
         T01.CUNOCV, T04.CUSNM, T01.IVDTCV, T02.LISQVS,
         T03.SCDSSS, (T03.SCAMSS)
   FROM  AMFLIB/CUSINVL0 T01
          inner join AMFLIB/CIVSPCL0 T02
           on (    T01.INVNCV = T02.INVNVS
               AND T01.INSQCV = T02.INSQVS )
          inner join AMFLIB/CUSMAS   T04
           on (    T01.CONOCV = T04.COMNO
               AND T01.CUNOCV = T04.CUSNO  )
          inner join AMFLIB/COSSPCL0 T03
           on (    T02.ORDRVS = T03.ORDRSS
               AND T02.SHSQVS = T03.SHSQSS
               AND T02.SCSQVS = T03.SCSQSS )
   Where     T01.CONOCV = 01
     AND     T01.ICDTCV BETWEEN 1040701 AND 1040731
     AND     T03.SCCDSS = '2'
   ORDER BY  GLACCT ASC, T01.INVNCV ASC

Now, I would like to change the inner join to CUSMAS (T04) to a left join,
on the off-chance we have a missing customer record.  *But*, I would like 
to
use a substituted value, like "* Customer # not found *' in the customer
name field (T04.CUSNM) when there is no customer record.

The frosting would be if I could specify "* Customer nnnnnnnn not found 
*',
where 'nnnnnnnn' is the customer number.

Oh, and I would ask again, why did RTVQMQRY surround the T03.SCAMSS field 
in
the SELECT in parentheses?

BTW, this exercise demonstrates the beauty of SQL.  Even though I'm not
proficient in it yet, I was able to take a working *QRYDFN, convert it to
SQL and get the same results (the caveat being the "Match Type = 1" is the
only join that will convert properly), tweak it do explicit joining, 
testing
it along the way in interactive SQL and in Ops Nav.  Last night I copied 
the
SQL into an RPGLE program, the mainline for which is so simple.  To do 
this
in native I/O would have required building a workfile with CHAINs and
SETLL/READEs, then reading it, probably two programs there, with a third 
to
call the two; a PITA.  It's blazingly fast, too: 200,000 records in the
primary file, 100,000 in T02, 100,000 in T03, and 5,000 in T04; end up 
with
about 1200 rows in less than two seconds!  Obviously, YMMV and, yes, we've
got the horses. <g>

tia,
db

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.