|
Thanks, Terry, I may have an answer. I think you can use a left outer join of the the 2 files. This kind of join returns all records from the left hand file with nulls where the right hand file values should be, if there is not a match. In other words, you will get all the exception file records listed. If there is a match, the columns from the history file will be included. If no match, those columns will be set to NULL. The COALESCE replaces NULL columns with the text you want when there is no match. select excfile.partno, coalesce(histfile.instructions, 'part ' || excfile.partno || ' not received') from excfile left outer join histfile on excfile.partno = histfile.partno where histfile.histdate between :startdate and :enddate There might be other fields you need in the list, I just used the part number. BTW, if the part number is numeric, you will need to convert it to text in order to concatenate it into the "not received" text. Also, notice the colons (:) before startdate and enddate. This indicates host variables - RPG variables whose values will be inserted into the SELECT statement. This makes for and easy way to programatically handle the daterange. Use and appropriate date format to match your data. HTH Vern -------------- Original message -------------- > Here are the details. > > The history file contains information on all parts received. The > exception file has certain parts that require additional quality testing > that the vendor does not do. I need to read the history file for a > certain time period and if I find that one of the parts in the exception > file has been received I write the testing instructions to the print > file. If one of the parts in the exception file is not found in the > history file for the time period selected, I need to print a line that > says something like "part 123 not received". > > With an OPNQRYF over the history file, I can read the exception file > then chain to history. If I find a record, I can write the the testing > instructions. If no record is found then I can write the "part not > received" line. > > I can join both files with sql, but I don't know how to make it write > the "part not found" line. Some one mentioned coalesce() and I am in the > process of researching it to see if it will do what I need. > > > Thanks, > > > Terry > > > > message: 6 > > date: Wed, 14 Sep 2005 22:05:47 +0000 > > from: vhamberg@xxxxxxxxxxx > > subject: Re: MIDRANGE-L Digest, Vol 4, Issue 1736 > > > > Does the OPNQRYF include a JOIN to the exception file? If not, how is it > > that you don't need to chain to the exception file in that case? At any > > rate, you can certainly join to the exception file with SQL, too. Again, > > can you show us the details? It's hard to comment on what we do not know. > > > > Cheers > > Vern > > > > -------------- Original message -------------- > > > > > It's true that I can get the same selection with SQL that I can get with > > > OPNQRYF. The excess I/O comes in because I can't chain to the SQL > > > selection like I can with the OPNQRYF. The only way I know to do this > > > with SQL would be to select the records from the history file based on a > > > date range, then fetch each record and chain to the exception file to > > > see if item selected is found there. This would mean chaining out to the > > > exception file hundreds of times rather than chaning to the history file > > > 4 or 5 times. > > > > > > Thanks, > > > > > > > > > Terry > > > > > > > > > > > > > > -- > http://www.fastmail.fm - Same, same, but different? > > > -- > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list > To post a message email: MIDRANGE-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/midrange-l > or email: MIDRANGE-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/midrange-l. >
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.