Re: Query/400 question

At 08:29 PM 12/4/98 +0700, you 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


If I understand the problem correctly, you can do it with 2 queries:
Query 1:
Use only Stock# and POdate fields.  Sort on Stock#.  Report Break 1 on
Stock#.  In summary functions, choose Maximum POdate.  In output specs,
select database file and summary only.

Query 2: Join file created in query 1 with your original file, matched
records only.  Join on the fields stock# and PO date. That's it.

If that's not it, let me know and I'll try again.

Jim
>
>+---
>| 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
+---