RE: QUERY TO SQL

Rick,
that didn't work either.  it doesn't pick up the records from first file
where there is no match in the second file.

i think in one of my many attempts i had tried this, but at this point i'm
not sure anymore.  seems like this should be real simple.


-----Original Message-----
From: Richard B Baird [mailto:rbaird@esourceconsulting.com]
Sent: Tuesday, March 19, 2002 9:17 AM
To: midrange-l@midrange.com
Subject: QUERY TO SQL



Mike,

you were close with your 'left outer join' attempt, but you need to
separate your 'join on' from your 'where' clauses.
try this:

SELECT UMACT, UMPRM, UMSTS, UOACT, UOARA
FROM uact left outer join
     uactov
  on umact = uoact
where Uoara = '' and
      umsts in('AC', 'PN')

Rick

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