David,
I've found in the past that if Queries get complex, with lots of selections and file matches, that the results can be not always what you expect. Or maybe I'm just not very good with Query!
Are you an RPG programmer? If so I would recommend writing a program to get what you want.
I've recently written a program that did something similar - I needed to retrieve the receipt transactions for a series of parts, showing all transactions in descending date sequence. In our system we rarely override the ITH Transaction Date, so effectively the sequence of records on ITHL01 is oldest to latest within Part Number.
With an RPG program, for each of your parts you would do a SETGT to ITHL01 on your Part Number, followed by a READPE on Part Number. Check the ITH record against your required selection criteria and when you get a match, that's the latest transaction for the part.
If you wanted to, you could create your own logical file over ITH keyed by Part Number / Date (descending) / Time (descending) then process this via SETGT and READPE instead of using ITHL01.
Hope this helps.
Tom Molyneux
NOV Mono
MIS Department
Tel. +44 (161) 214 2142
Fax. +44 (161) 214 2344
E:mail Tom.Molyneux@xxxxxxx
-----Original Message-----
From: bpcs-l-bounces+tom.molyneux=nov.com@xxxxxxxxxxxx [mailto:bpcs-l-bounces+tom.molyneux=nov.com@xxxxxxxxxxxx] On Behalf Of David.ANTONISSE@xxxxxxxxxxxxxxxx
Sent: 21 October 2010 08:39
To: bpcs-l@xxxxxxxxxxxx
Subject: [BPCS-L] Item with last transaction date
Good morning dear consultants!,
with downstanding two queries, i try to get a list of all transactions in
query 1, and in the second one i try to find the last used transaction date
for each item (of query 1). I would like to have a list with only the last
transaction with the items.
I use BPCS version 6.1/release GA.
Can you assist me with this?
Thank you!
Query 1:
Used files: ITH, CICL01, IIML01, ILIL01,
Select records:
ICFAC EQ 'AA'
AND TWHS LIKE 'B%'
AND TTYPE NLIST 'C' 'A' 'T'
AND TTYPE NLIKE 'T%'
AND LWHS LIKE 'B%'
AND TCLAS NLIKE 'P%'
AND TCLAS NLIKE 'R%'
Query 2:
Used files: Output file 1, ILI.
Select records:
LWHS LIKE 'B%'
AND TCLAS NLIKE 'P%'
AND TCLAS NLIKE 'T%'
Matched records with primary file and TPROD EQ LPROD
I don't get a good list myself. Do you have any information to get this?
Thank you!
Rgds,
David AntonissePlease consider the environment before printing this e-mail. --------------------------------------------------------------------------------------------------------------------- Discover Impress on
http://www.impressgroup.com The information contained in this e-mail and any attachments is strictly confidential and may be legally privileged.It is intended solely for the use of the individual or entity to whom it is addressed. If you are not the named addressee, you are hereby notified that it is prohibited and may be unlawful to disclose, distribute, store, copy the information in any medium or usage for another purpose. ---------------------------------------------------------------------------------------------------------------------
--
This is the BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/bpcs-l.
Delivered-To: Tom.Molyneux@xxxxxxx
Mono Pumps Limited
Registered in England No. 300721.
Registered Office : Martin Street, Audenshaw, Manchester. M34 5JA
midrange.com
