RE: SQL table joins: Join vs. Where

["Dan Bale" <dbale@xxxxxxxxxxxxx>]

Vern, I checked out the Query Management Programming manual, scanned on
"join" and found nothing of value.

I modified an unfruitful archive search and found some other relevant
information.  So this is what I've modified my original SQL to:

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  ),
        AMFLIB/COSSPCL0 T03
  Where (    T02.ORDRVS = T03.ORDRSS
         AND T02.SHSQVS = T03.SHSQSS
         AND T02.SCSQVS = T03.SCSQSS )
    AND     T01.CONOCV = 01
    AND     T01.ICDTCV BETWEEN 1040701 AND 1040731
    AND     T03.SCCDSS = '2'
  ORDER BY  GLACCT ASC, T01.INVNCV ASC

The change is that I moved the pseudo-join Where's to an inner join and
removed the "selection" from the Where clause for the T01 <-- T02 and T01
<-- T04 joins.  This works as well as the original.

Where I failed was trying to join T03 to T02; I'm getting errors like
"Duplicate table designator T02 not valid" and "Column qualifier or table
T03 undefined".  Here's what I've tried:

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  ),
        AMFLIB/CIVSPCL0 T02 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

What am I missing?

tia,
db