× 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: bill.blalock@xxxxxxxx

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.

Actually, Bill, it works fine for me. It just takes a LOOOONG time because
of the nature of the query. Here's what worked:

select I_ID, I_EVENT, I_TMSP, coalesce(O_OWNER, '*NONE')
from invent left outer join owner on I_ID = O_ID
where O_TMSP = (select MAX(O_TMSP) from owner
where O_ID = I_ID and O_TMSP <= I_TMSP)

I named my tables OWNER and INVENT, and I called my control number field ID
and my status field EVENT.


O_ID O_OWNER O_TMSP
1 JOE 80915120100
1 BILL 80915121000

I_ID I_EVENT I_TMSP
1 EVENT 1 80915120200
1 EVENT 2 80915120300
1 EVENT 3 80915120400
1 EVENT 4 80915121200
1 EVENT 5 80915121300


I get this:

I_ID I_EVENT I_TMSP COALESCE
1 EVENT 1 80,915,120,200 JOE
1 EVENT 2 80,915,120,300 JOE
1 EVENT 3 80,915,120,400 JOE
1 EVENT 4 80,915,121,200 BILL
1 EVENT 5 80,915,121,300 BILL

Exactly what you wanted. I do have an issue that the LEFT OUTER JOIN isn't
working quite the way I thought it would, and frankly I don't have a good
answer for it. Without going into a lot of detail, the only way around this
that I can find is using a CTE with a dummy first record UNIONed to the
OWNER file.

I suppose the easier way would just be an exception SELECT:

Select * from INVENT where I_TMSP <
(SELECT MIN(O_TMSP) FROM OWNER WHERE I_ID = O_ID)

Then UNION the results of that with the original. Man this stuff gets ugly
quick. I hope an SQL expert can show us a better way!


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.

Yup. It's definitely the right tool for the job.


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).

And it's not one that SQL is particularly well suited for: trying to
correlate non-matching tables. It can be done, of course, but if you don't
have the right indexes in place, it could take a really long time.

Joe



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.