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

Vern,
Thanks!! That worked out great. Talk about a win win situation, the user
got what they wanted and I learned something new.

Thanks again,


Terry 


> message: 5
> date: Thu, 15 Sep 2005 16:07:02 +0000
> from: vhamberg@xxxxxxxxxxx
> subject: 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. 
> > 
> 
> ------------------------------
>