× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.