Need to write SQL to do what being done by 3 QUERY /400

I have to write a Logical view or 2 to process legacy Query/400. The first query creates a temp file that is input to the second query (and second creates temp to the 3rd query). The purpose is that we want to see which customers do not have a 'correction' order which we get in the second query.

In the first query, it selects from OEINH1 and matches to Address_Table to get the customer email address, and selects on

T01.IHDOCD        DOCUMENT DATE                
T01.IHENT#        ENTITY NUMBER                
T01.IHSFX#        SUFFIX NUMBER                
T01.IHINV#        INVOICE NUMBER               
T02.ADINTA        INTERNET ADDRESS

These columns and record selection:

IHORDT            EQ     'INT'        **** order type              
AND    IHVIAC            NLIST  'PML' 'FCM'               
AND    ADSFX#            EQ     '000'

Second query matches on IHENT# (cust #) AND selects on this:
```
IHORDT            LIST   'COR' 'COE'     
```
and writes all columns from both the first temp and the same OEINH1 to a 2nd temp file.

This is problematic to redo this exactly in SQL because there are duplicate field names. Basically what is needed i think is to take the first temp file and then get the other 2 order types that this customer has (COR COE)

This sb something like this

Select * 
from TEMP FILE1 
where ENT in (Select * 
              from OEINH1 
              where IHORDT in ('COR' "COE')

does this make sense according to what the query/400 is doing?

The final QUERY/400 is then comparing the 2 temp files using UNMATCHED for the type of join. So then we can know which records are in the first pass but not in the second temp file.

Solution

Using Query to create a temporary file, then another Query to create another temporary file and finally a third query to process that file is kind of opposite of the way we think about SQL. SQL is about manipulating sets of records.

This is untested:

select h.IHDOCD, h.IHENT#, h.IHSFX#, h.IHINV#, a.ADINTA
from OEINH1 h 
  join Address_Table a
    on h.ihent# = a.customer_id
where h.IHORDT = 'INT'
  AND h.IHVIAC not in ('PML' 'FCM')
  AND ADSFX# = '000' 
  and h.ihent# in (
    select cor.ihent# 
      from oeinh1 cor
      where cor.IHORDT in ('COR' 'COE'))