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

["Dan Bale" <dbale@xxxxxxxxxxxxx>]

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