RE: QUERY TO SQL

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

RTVQMQRY does not handle the various joins properly. Here is the text from
Query Management manual that pertains, from Chapter 12. Using Query/400
Definition Information in Query Management:

>If the acceptability of the Query/400 result depends on any of the
>following, the Query Management
>result will probably be unacceptable. Parenthesized phrases indicate the
>potentially unacceptable
>system action for each item:
>– Unmatched join with primary file (A matched join is performed)
>– Matched join with primary file (A matched join is performed)

It will never create a JOIN specification, only the older WHERE cluse
version for joins. But you can take the join criteria and convert it to the
appropriate JOIN, Query type 2 -> left outer join, type 3 -> exception join.

Also, is the selection on UOARA for an empty character field, or one with
blanks? Might matter.

At 09:16 AM 3/19/02 -0500, you wrote:
>SELECT
>   ALL       (T01.UMACT), T01.UMCKD, T01.UMPRM, T01.UMPCN, T02.UOARA,
>T01.UMTYP,
>             T01.UMSTS
>   FROM      RGFILES/UACT T01,
>             RGFILES/UACTOV T02
>   WHERE     T01.UMACT = T02.UOACT
>     AND(    UOARA = ''
>     AND     UMSTS IN ('PN', 'AC'))
>   ORDER BY  001 ASC
>
>
>i only get 2 records.
>
>one thing i forgot to mention is that there are records in the first file
>that don't have a match in the second file, but need to be picked up in the
>query.  that seems to be where the SQL breaks down.
>
>****************** End of data ****************************************
>
>-----Original Message-----
>From: Dan Bale [mailto:dbale@samsa.com]
>Sent: Tuesday, March 19, 2002 9:12 AM
>To: midrange-l@midrange.com
>Subject: RE: QUERY TO SQL
>
>
>What does the RTVQMQRY show for the SQL?
>
>Dan Bale
>SAMSA, Inc.
>989-790-0507
>DBale@SAMSA.com <mailto:DBale@SAMSA.com>
>   Quiquid latine dictum sit altum viditur.
>   (Whatever is said in Latin seems profound.)
>
>
>-----Original Message-----
>From: midrange-l-admin@midrange.com
>[mailto:midrange-l-admin@midrange.com]On Behalf Of Smith, Mike
>Sent: Tuesday, March 19, 2002 9:05 AM
>To: 'MIDRANGE-L@MIDRANGE.COM'
>Subject: QUERY TO SQL
>
>
>I have a standard query that i am trying to convert to an SQL statement but
>i cannot get the same results in my SQL and query
>
>the query  has file UACT, UACTOV
>
>it is a  '2' (matched records with primary file) on UMACT = UOACT
>
>in the record selection  UOARA = ' ' and UMSTS LIST 'AC , 'PN'
>
>this query produces 5 records.
>
>
>so i've tried numerous sql statements to duplicate this.
>
>SELECT UMACT, UMPRM, UMSTS, UOACT,
>UOARA FROM uact left outer join uactov on umact = uoact
>and Uoara = ''
>
>SELECT UMACT, UMPRM, UMSTS, UOACT,
>UOARA FROM uact, uactov WHERE umact = uoact and umsts
>in('AC', 'PN') and UOARA = ' '
>
>
>and others.
>
>i've even tried doing a ' RTVQMQRY'
>
>i've already written the report using Native I/O(SMILE JOE)  but want to
>figure out the correct SQL statement for future reference.
>
>anybody have ideas.
>
>thanks
>
>mike
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>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@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.