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

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)

message: 4
date: Mon, 17 Sep 2007 14:32:34 -0500
from: Mark Adkins <adkinsm@xxxxxxxxxx>
subject: Re: Need ideas for SQL join select to replace logical view

I don't think I saw a solution, so I thought I'd post. The challenge is

that you have a range that is defined by two records. I assume the
transfer of ownership takes at least a second. ie. A person sells on

second and another person buys on the next second.

Regards,

Mark

SELECT A.O_CONTROL#, A.O_OWNER, B.I_STATUS, B.I_TMSP

FROM (SELECT A.O_CONTROL#, A.O_OWNER, MIN(A.O_TMSP) AS OWNERSTART,
MIN(IFNULL(B.O_TMSP,
TIMESTAMP('30000101000000')) - 1 SECOND)
AS OWNEREND
FROM OWNER AS A
LEFT OUTER JOIN OWNER AS B
ON A.O_CONTROL# = B.CONTROL# AND A.O_TMSP < B.O_TMSP
GROUP BY A.O_CONTROL#, A.O_OWNER) AS A

INNER JOIN INVENTORY AS B

ON A.O_CONTROL# = B.I_CONTROL# AND
B.I_TMSP BETWEEN A.OWNERSTART AND A.OWNEREND

ORDER BY 1,2,4

message: 6
date: Fri, 14 Sep 2007 16:02:59 -0500
from: bill.blalock@xxxxxxxx
subject: Need ideas for SQL join select to replace logical view

I am trying to do this problem with SQL instead of logicals. There

the key fields of two tables.

INVENTORY A table
I_CONTROL#
I_STATUS (really a lot of informatoin)
I_TMSP

OWNER B table
O_CONTROL#
O_OWNER
O_TMSP

The status of an item is in the INVENTORY file, the owner of the item

in the OWNER file. The owner of an item (control#) is independent of

its

status.

There is always one owner of each item. During the course of time the

owner of an item in inventory may change.

The status is what happens to the item in inventory over time.

Each items is created with an initial ower and inventory record.

Looking back in time the owner of an item in inventory is
B.CONTROL# = A.CONTROL# AND B.TMSP <= A.TMSP

For a given INVENTORY record using logical files I would

SETGT(I_CONTROL, I_TMSP) OWNER
READPE(I_CONTROL) OWNER

How would an SQL query be constructed to give a result of

I_CONTROL#, I_STATUS, I_TMSP, O_OWNER

Given that the O_OWNER is the record with O_TMSP equal to or earlier

than

the I_TMSP


AN example

I_CONTROL# = 1
I_STATUS = purchased the car
I_TMSP = 2/1/06@4:00

O_CONTROL# = 1
O_OWNER = Bill
O_TMSP = 2/1/06@4:00

I_CONTROL# = 1
I_STATUS = drove to Tuscaloosa
I_TMSP = 2/2/06@3:00

I_CONTROL# = 1
I_STATUS = drove to birmingham
I_TMSP = 2/3/06@4:00

I_CONTROL# = 1
I_STATUS = washed car
I_TMSP = 2/4/06@10:00

I_CONTROL# = 1
I_STATUS = traded in car
I_TMSP = 2/5/06@4:00

O_CONTROL# = 1
O_OWNER = Charlie
O_TMSP = 2/6/06@11:00

I_CONTROL# = 1
I_STATUS = drove car off lot
I_TMSP = 2/6/06@11:10

I_CONTROL# = 1
I_STATUS = drove car to memphis
I_TMSP = 2/7/06@12:00

CONTROL STATUS I_TIMESTAMP OWNER
1 purchased the car 2/1/06@4:00 Bill
1 drove to Tuscaloosa 2/2/06@3:00 Bill
1 drove to birmingham 2/3/06@4:00 Bill
1 washed car 2/4/06@10:00 Bill
1 traded in car 2/5/06@4:00 Bill
1 drove car off lot 2/6/06@11:10 Charlie
1 drove car to memphis 2/7/06@12:00 Charlie

Any thoughts on how to write a QUERY to produce this result set? On

join I want only one record from OWNER, the record where CONTROL# are
equal and whose timestamp is equal or earlier to the one from the
INVENTORY record.

Thanks!