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
<midrange-l@xxxxxxxxxxxx>
cc: (bcc: Bill Blalock/TUS/US/Certegy)
Subject: RE: Need ideas for SQL join select to replace
logical view
From: Joe Pluta
select I_CONTROL#, I_STATUS, I_TMSP, O_OWNER
from inventory join owner on I_CONTROL# = O_CONTROL#
where O_TMSP = (select MAX(O_TMSP) from owner
where O_CONTROL# = ICONTROL# and O_TMSP <= I_TMSP)
A couple of additional thoughts. First, this is almost exactly the kind
of
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
system
would read the records for you. On each INVENTORY record you would
already
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
that
do not have corresponding owner records (that is, any inventory record
whose
timestamp is earlier than the earliest owner record for that control
number)
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
where O_CONTROL# = ICONTROL# and O_TMSP <= I_TMSP)
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
default
value (in this case *NONE) for those records.
BTW, this is not really a knock on SQL. Exception logic is a pain in
either
case. It's just that since SQL is so monolithic in nature I seem to end
up
with really big, long statements once I start adding in all of the
exception
code.
Joe
--
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.
------------------
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.
------------------
midrange.com
