Re: MIDRANGE-L Digest, Vol 4, Issue 1747 (CALLPRC & CRTBNDCL)

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. 
>