RE: SQL table joins: Join vs. Where

Also, the a query written using the join systax is usually significatly
faster than an equivalent query using the where clause.

Jay Himes
Liberty University

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Wednesday, August 11, 2004 4:29 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL table joins: Join vs. Where

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.


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