Hi Joe:

The SQL you suggested didn't quiet do the job when I tested it under query
manager. It started looping and reprocessing records. I am still working
with it.

Building a work file to represent the data needed is very easy with RPG
for this problem. Using level breaks and matching records is even less IO
than my proposed solution.

I am trying to wrap my head around SQL. This is a very basic problem in
my inventory project ... matching the owner of an inventory item at the
point in time with what happened to that item. In this case the ownership
of the item is one process (hence the owner file) and what happens to
inventory is another process (hence the status file).

Thanks both of you for your help.

Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/15/2007 11:41 AM
Please respond to Midrange Systems Technical Discussion

To: Midrange Systems Technical Discussion
cc: (bcc: Bill Blalock/TUS/US/Certegy)
Subject: RE: Need ideas for SQL join select to replace
logical view

From: Joe Pluta

from inventory join owner on I_CONTROL# = O_CONTROL#
where O_TMSP = (select MAX(O_TMSP) from owner

A couple of additional thoughts. First, this is almost exactly the kind
situation that matching record logic was built for. If you coded OWNER as
primary (using record indicator 01) and INVENTORY as secondary (02), then
set up M1 and L1 on CONTROL# and L2 on TMSP, basically the operating
would read the records for you. On each INVENTORY record you would
have the associated OWNER record read in. Unless, of course, there was no
OWNER record, in which case you'd have 02NMR and you could print the
appropriate error.

Second, this is the part I always have a problem with in SQL; coding for
exception conditions. In my SQL statement above, any inventory records
do not have corresponding owner records (that is, any inventory record
timestamp is earlier than the earliest owner record for that control
will not be shown in the report.

Instead, you need to do something like this:

select I_CONTROL#, I_STATUS, I_TMSP, coalesce(O_OWNER, '*NONE')
from inventory left outer join owner on I_CONTROL# = O_CONTROL#
where O_TMSP = (select MAX(O_TMSP) from owner

Left outer join will create rows with O_OWNER set to NULL when an OWNER
record is not found, while the COALESCE will allow you to specify a
value (in this case *NONE) for those records.

BTW, this is not really a knock on SQL. Exception logic is a pain in
case. It's just that since SQL is so monolithic in nature I seem to end
with really big, long statements once I start adding in all of the


This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot 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 here. If you have questions about this, please contact