RE: QUERY TO SQL

["Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>]

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.