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



Esteemed listers:

Have four tables, with the following relationships:

T01 < --- T04
     \--- T02 <--- T03
     \--- T02 <--- T03
     \--- T02 <--- T03
     \--- T02 <--- T03

T01 (Invoice Header) is the primary file
T02 (Invoice Specials) has many records to one T01 record
T03 (Order Specials) has one record to one T02 record
T04 (Customer Master) has one record to one T01 record

This needs to be an "inner" join, i.e. no row returned for a T01 row that
does not have all of the other three rows.  Did this originally in WRKQRY,
then did a RTVQMQRY to get the SQL for it:

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,
            AMFLIB/CIVSPCL0 T02,
            AMFLIB/COSSPCL0 T03,
            AMFLIB/CUSMAS   T04
  WHERE (   T01.INVNCV = T02.INVNVS
    AND     T01.INSQCV = T02.INSQVS )
    AND (   T02.ORDRVS = T03.ORDRSS
    AND     T02.SHSQVS = T03.SHSQSS
    AND     T02.SCSQVS = T03.SCSQSS )
    AND (   T01.CONOCV = T04.COMNO
    AND     T01.CUNOCV = T04.CUSNO  )
    AND  (  T01.CONOCV = 01
    AND     T01.ICDTCV BETWEEN 1040701 AND 1040731
    AND     T03.SCCDSS = '2'      )
  ORDER BY  GLACCT ASC, T01.INVNCV ASC

Why doesn't RTVQMQRY convert the WRKQRY joins to SQL joins?  Is there a
performance issue between one or the other?

Other questions:
2) Notice the last field on the Select, T03.SCAMSS is put in parentheses.
In the WRKQRY, I used the summary total function on this amount field.  But
running this SQL does not show any total line.

3) I also specified a break on the first sort field GLACCT in the WRKQRY,
but this did not "convert" to the SQL.  This will wind up as embedded in an
RPG program, so I imagine I'll have to manage the level breaks myself?
I.e., monitor change to GLACCT?

tia,
db


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.