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



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.