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

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> 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