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

RTVQMQRY is broken and probably will not be fixed, AFAIK. It has never converted join types 2 or 3 to the equivalent SQL. It always makes them into WHERE clauses. Probably because it is an old tool that was written before JOIN support was added to iSeries SQL.

Using the now-available JOIN syntax would make this a piece of cake. Doing the varous JOIN options with WHERE clauses was relatively tough and involved UNIONs and NOT EXISTS constructions with subqueries. Not my idea of a good time!

Things like total lines, etc., are a "presentation" part of Query/400 queries. SQL will only retrieve the data, not present it. For that you want to use QM forms (*QMFORM data type). See, Query/400 *QRYDFNs combine data retrieval and presentation in one object. QM queries separate the 2 functions. You can get something close to the output of a *QRYDFN by using the RTVQMFORM command. Again, there are differences. Some things are harder to do with QMFORMs, some things are wonderful. E.g., you can wrap a long text field within a column in a QMFORM. You can also get some pretty fancy summary output by combining aggregate functions in the SQL statement with summary functions in the form.

There is documentation on this stuff in the Query Management Programming manual - not the Query Manager User Guide, which is pretty basic. There is also the ANZQRY command that will tell you the possible conversion problems. The same warnings are sent to your job log when using the RTVQMQRY command.

I know this does not help much--
Vern

At 02:48 PM 8/11/2004, you wrote:

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

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