RE: Query/400 question

[Rich Dotson <rdotson@xxxxxxxxxxxxxxxxxxxx>]

Satria,

Try this:

1. Create a source file MYLIB/QQMQRYSRC
2. Create a member (MBR1) and place the following in it:

        0001.00 SELECT stock, desc, unitcost, po, podate, vendor
        0002.00   FROM MYLIB/FILE1 a
        0003.00     WHERE EXISTS (SELECT count(*) FROM MYLIB/FILE1 b
        0004.00             WHERE  a.stock = b.stock
        0005.00             HAVING a.podate = MAX(b.podate))
        0006.00       ORDER BY stock

3. In your CL program place the following:

        0025.00         CRTQMQRY   QMQRY(QTEMP/QRY1) +
        0026.00                      SRCFILE(MYLIB/QQMQRYSRC) SRCMBR(MBR1)
        0027.00         STRQMQRY   QMQRY(QTEMP/QRY1) OUTPUT(*OUTFILE) +
        0028.00                      OUTFILE(QTEMP/QRYPF)

Change MYLIB to the correct library and FILE1 to the correct file.
You can change the OUTPUT to *, *PRINT or *OUTFILE.

This should give you the results you want.  I don't know what the response time 
will be.
If you decide to try it,  let me know how it works.

HTH,

Rich Dotson
RT3 Consulting Services, Inc.
1-216-556-1710

rich_dotson@centuryinter.net

On Friday, December 04, 1998 8:29 AM, Satria Suryahadi 
[SMTP:satria@jakarta.wasantara.net.id] wrote:
> Dear query/400 experts...,
> 
> End user request concerning modification of an interactive CL pgm which call
> 6 queries I create last year is really bugging me. Is anyone out there can
> give me tips to make my queries works better and efficient?. 
> Here is the case, there is one transaction physical file that looks like this:
> 
> (Sorted by Stock#-Ascending, and PODate-Descending)
> 
> Stock#          Description             UnitCost PO#    PODate     Vendor#
> A0101001        BUSHING; 1 KV/250 A     48.000   DE0021 98/10/20   FE001
> A0101001        BUSHING; 1 KV/250 A     46.000   PE0012 98/05/13   LE012
> A0101001        BUSHING; 1 KV/250 A     44.000   DE0003 97/11/24   LA123
> A0101001        BUSHING; 1 KV/250 A     46.000   DE0021 97/10/05   FD034
> A0101001        BUSHING; 1 KV/250 A     47.000   DE0021 98/10/20   LC005
> 
> A0101002        COPPER BLA BLA..       123.000   CE0123 98/05/12   DE012
> A0101002        COPPER BLA BLA..       130.000   BE0221 98/01/13   LE013
> A0101002        COPPER BLA BLA..       120.000   DE0028 97/09/24   LA123
> A0101002        COPPER BLA BLA..       100.000   LE0321 96/10/04   FD034
> A0101002        COPPER BLA BLA..       128.000   BE0009 95/08/21   LC005
> 
> A0101003        OTHER  BLA BLA..       490.000   PE0320 98/09/13   CE013
>                                                                 More...
> 
> 
> Expected query result is to get the most current UnitCost of every Stock#,
> which looks like this:
> 
> 
> Stock#          Description             UnitCost PO#    PODate     Vendor#  
> A0101001        BUSHING; 1 KV/250 A     48.000   DE0021 98/10/20   FE001      
>  
> A0101002        COPPER BLA BLA..       123.000   CE0123 98/05/12   DE012
> A0101003        OTHER  BLA BLA..       490.000   PE0320 98/09/13   CE013
>                                                                 More...
> 
> 
> My CL pgm call 6 queries which process 5 physical files and heavily use
> QTEMP library, NOW it takes 10 minutes to get the result (F10, CISC). I try
> to find the solution in Query/400 manual but I can't find useful tips.
> 
> Please don't mention RPG at this time, since I'm in hurry  :-)
> 
>                              
> Thanks in Advance,                   
> 
> 
> Satria
> 
> +---
> | 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
+---