0

I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this

CREATE TABLE "SOMEDATA" (
    "ID" INTEGER NOT NULL,
    "ITEMNAME" VARCHAR2 NOT NULL,
    "LINKLEFT" VARCHAR2 DEFAULT NULL,
    "LINKRIGHT" VARCHAR2 DEFAULT NULL,
    "CREATE_TIMESTAMP" TIMESTAMP NOT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL
)

The purpose of this table is to store timestamped records which may link to other records, similar to a hierarchy. My challenge is build a query that at any given specific timestamp, and for a given itemname, recursively fetch the latest versions of the rows that chain to the right of the item. E.g. in the following data:

+----+----------+----------+-----------+---------------------+------------+----------+--+
| Id | Itemname | Linkleft | Linkright |  create_timestamp   |   value1   |  value2  |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+
|  1 | item1    | (null)   | item2     | 23/08/2014 00:00:00 | Test       | Data     |  |
|  2 | item2    | item1    | item3     | 23/08/2014 00:01:25 | Some       | More     |  |
|  3 | item3    | item2    | (null)    | 23/08/2014 00:05:00 | Even       | MoreData |  |
|  4 | item2    | item1    | item3     | 24/08/2014 00:00:00 | Changed    | Data     |  |
|  5 | item1    | (null)   | item2     | 25/08/2014 12:37:02 | New        | Better   |  |
|  6 | item3    | item2    | item4     | 27/08/2014 00:00:00 | Lipsum     | Lopsum   |  |
|  7 | item4    | item3    | (null)    | 27/08/2014 00:01:00 | Additional | Stuff    |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+

For the query ITEMNAME='item1' and CREATE_TIMESTAMP=23/08/2014 14:58:59, I need to return records #1,2 and 3. This is because these are the latest linked records that link to the latest record for 'item1' at that particular point in time. However, for the query ITEMNAME='item1' and CREATE_TIMESTAMP=01/01/2015 12:00:00, I need to return records #5,4,6 and 7, because at that time, these records were the latest versions.

I've been playing about with Oracle's CONNECT BY PRIOR to build something like

SELECT * FROM "SOMEDATA" START WITH "ITEMNAME"="item1" CONNECT BY PRIOR "LINKRIGHT"="RIC"

This obviously chains the records together, but pays no attention to timestamps and returns all values for rows which match.

It seems that hierarchical records is a nicely solved problem, but not in the case where you want to also store the historical values for records in the hierarchy and query for any time. Is this sort of thing possible in this table structure, or am I heading down the wrong track entirely?

  • Your sample data is incomplete or confusing. I could understand 23/08/2014 filter, but how the heck 25/08/2014 and 27/08/2014 mapped to 1/1/2015 ? what is the logic for timestamp? – Raj Jan 22 '15 at 16:00
  • I've updated it to hopefully be a bit clearer. The query is asking at a specific point in time, what was the latest view on the hierarchy. So on the first query, the latest value for item1 is row #1, and that links to the item2, the latest value at that time was row #2. – growse Jan 22 '15 at 17:09
  • You probably need another query to sort 'item1' entry that is the max(timestamp) less than or equal to your asking timestamp. Then make sure that item1 entry and that max(timestamp) is specified as starting point. – Raj Jan 22 '15 at 17:16
  • If I explicitly set the timestamp in the 'START WITH' clause (START WITH "ITEMNNAME"="item1" AND TIMESTAMP=?), the CONNECT BY clause still returns multiple rows for each related. If I could somehow group these by distinct(ITEMNAME) and return the record foreach ITEMNAME with the largest timestamp below a certain value, that'd probably be what I need. I've no idea how to do that though. – growse Jan 22 '15 at 17:59
-1

how about:

with preresult as (
SELECT id,itemname,
   linkleft,
   linkright,
   create_timestamp,
   value1,
   value2,
   ROW_NUMBER ()
      OVER (PARTITION BY itemname ORDER BY create_timestamp DESC)
      rownumber
   FROM somedata
   WHERE create_timestamp < :timestamp)  
select * from preresult where rownumber < 2

and enter as :timestamp the desired date and time

hth, macfly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.