RE: SQL Troubles

     John,
     
     I've found that DB2/400 performs better when using the formal join 
     syntax. For your problem, I'd suggest an inner join to target data 
     that exists in both tables. The syntax follows the 
     
     FROM table1 INNER JOIN table2 ON (field1=field2)
     
     to build your result.
     
     
     
     
     SELECT T1.invoic FROM apinvoicv T1
             INNER JOIN apdist    T2 ON (T1.voucher=T2.voucher)
     WHERE T2.cknum = 8200
     
     hth,
     Eric DeLong


______________________________ Reply Separator
_________________________________
Subject: SQL Troubles
Author:  "John Hall" <SMTP:jhall@hillmgt.com> at EXCHANGE
Date:    3/2/01 4:54 PM


I am having trouble constructing the following SQL selection in an 
efficient manner
     
SELECT INVOIC FROM APINVOICV
WHERE VOUCHER IN
(SELECT VOUCHER FROM APDIST WHERE CKNUM = 8200 GROUP BY VOUCHER)
     
     
What I want to do is to pull out from APDIST the records matching CKNUM 
and then Get the records from APINVOICV which match those records.
     
What the system appears to be doing is solving for the IN clause for 
each record in APINVOICV.
     
I am running this with STRQMQRY.  On V4R4.
     
What is the proper way to construct this statement.  It will be created 
dynamically based on user input and then executed.  APINVOICV is keyed 
on VOUCHER.
     
TIA
     
John Hall
Home Sales Co.
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com. 
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.

| Questions should be directed to the list owner/operator:
david@midrange.com 
+---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---