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