|
midrange.com
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
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
