RE: Need ideas for SQL join select to replace logical view

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