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

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

Steve,

FYI
Order By cannot be specified in a view.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Steve Richter
Gesendet: Saturday, September 15, 2007 15:41
An: Midrange Systems Technical Discussion
Betreff: Re: Need ideas for SQL join select to replace logical view


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.