RE: SQL table joins: Join vs. Where -- MIDRANGE-L

The information on QRYDFN conversion is in chapter 12 of the V5R2 Query Management Programming manual. A search on QRYDFN might be more fruitful, too.

You have "AMFLIB/CIVSPCL0 T02" specified twice. The second one follows a comma, which is also invalid.

JOINs are cumulative. Each JOIN works against the result of the previous JOIN. So the columns you want for the JOIN to T03 are there from the previous set of JOINs. Try this:

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         -- this is the different line
          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

The difference is on line 11 of the statement you had.

HTH
Vern

At 04:43 PM 8/11/2004, you wrote:

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

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