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

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  IMO the problem is the database design.  Historical ownership can not 
be correctly represented with only a transaction time.  Such historical 
ownership would be a duration.  The ownership table also fails to denote 
that the *STORE is the owner between transactions which would complete 
known ownership history.
  I would define the OWNER table to have a column for both a FROM_TMSP 
and UNTIL_TMSP to designate the duration that the item is owned; the end 
of the duration is CURRENT TIMESTAMP when not defined.  Having done so, 
the problem of designating the OWNER for each sequential [historical] 
timestamped event should be more easily grasped, as ordered results of a 
LEFT OUTER JOIN WHERE EVENT_TMSP BETWEEN FROM_TMSP AND UNTIL_TMSP   The 
range for selection is defined in a row, rather than between that and a 
prior row.

  SELECT i.*, o.owner
  FROM                  INVENTORY I
        LEFT OUTER JOIN OWNER     O
  WHERE i.tmsp BETWEEN o.from_tmsp
                   AND ifnull(o.until_tmsp, current timestamp)
    AND o.owner <> '*STORE'

  An insert trigger on the OWNER file can enforce the rule that both 
the previous historical until-time and the new historical from-time will 
get the same timestamp value, and that the until-time of the new row 
gets the NULL value since the until-time for the current owner is 
indeterminate until the next insert defining the start of the next 
duration of ownership.

Regards, Chuck