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

Where you have T04.CUSNM in your SELECT ALL, you could substitute:

Coalesce(T04.CUSNM, '* Customer ' concat trim(T01.CUNOCV) concat ' not
found. *')

This will show you the missing customers when you change to your left join.

I haven't worked with RTVQMQRY much, so can't comment on your other
questions.

HTH,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713
-----Original Message-----
From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx] 
Sent: Thursday, August 12, 2004 08:10
To: Midrange Systems Technical Discussion
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.