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



Bill, here is a way to use sql views to modularize the problem:

-- renaming the inventory table to what I understand it to be.
-- ( also get rid of those icky rpg friendly field prefixes. )
create view ItemEvents as
(
select i_control# itemId,
i_status eventText,
i_tmsp eventTs
from inventory
) ;

-- same with the owner table
create view ItemOwner as
(
select o_control# itemId,
o_owner ownerName
from owner
) ;

-- this view selects all of the owner's events
create view OwnerItemEvents as
(
select a.OwnerName, a.itemId,
b.eventText, b.eventTs
from ItemOwner a
join ItemEvents b
on a.itemId = b.itemId
order by a.OwnerName, a.itemId, b.eventTs
) ;

-- the most recent event for each owner,item
-- note: just want the keys to the event. not any other info
-- like event description. This view could be used
-- in more than one query.
create view MostRecentOwnerItemEvent as
(
select a.OwnerName, a.itemId, max(a.eventTs)
from OwnerItemEvents a
group by a.OwnerName, a.itemId
) ;

-- grand finale. report the most recent item,owner events
select a.itemId, a.ownerName, a.eventTs, a.eventText
from OwnerItemEvents a
join MostRecentOwnerItemEvent b
on a.itemId = b.itemId
and a.ownerName = b.ownerName
and a.eventTs = b.eventTs
order by a.itemId, a.ownerName ;



On 9/14/07, bill.blalock@xxxxxxxx <bill.blalock@xxxxxxxx> wrote:

I am trying to do this problem with SQL instead of logicals. There are
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 is
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 the
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!

------------------
The information contained in this message is proprietary and/or
confidential. If you are not the intended recipient, please: (i) delete the
message and all copies; (ii) do not disclose, distribute or use the message
in any manner; and (iii) notify the sender immediately. In addition, please
be aware that any message addressed to our domain is subject to archiving
and review by persons other than the intended recipient. Thank you.
------------------
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



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.