RE: SQL Troubles

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

John,
Performance tuning SQL statements can be fun indeed.  One place to start is
STRDBG before you run your STRQMQRY.  The job log will show you the
decisions the database optimiser made while evaluating your SQL statement.

Ultimately, you might need to build a new logical, or it may be simpler to
use a work file:
CREATE TABLE QTEMP/VOUCHER (VOUCHER NUMERIC(5,0))

then use your variable select to fill it:
INSERT INTO QTEMP/VOUCHER 
  (SELECT VOUCHER FROM APDIST 
     WHERE CKNUM = &CKNUM 
     GROUP BY VOUCHER)

then your fixed statement to get "the answer":
SELECT INVOIC FROM APINVOICV  WHERE VOUCHER IN 
  (SELECT VOUCHER FROM QTEMP/VOUCHER)

Work files like this can sometimes cut your run time by an order of
magnitude.  That's why tuning is such fun...

Buck 

> -----Original Message-----
> From: John Hall 
> Sent: Friday, March 02, 2001 5:54 PM
> To:   MIDRANGE-L@Midrange.com
> Subject:      SQL Troubles
> 
> 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.
+---
| 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
+---