This discussion is archived
3 Replies Latest reply: Feb 22, 2013 12:08 PM by 942572 RSS

Point of time view with subquery

942572 Newbie
Currently Being Moderated
Hi,

I have one table with history-timestamp. I would like to create a view to get the row for a give time . For example:

Create tb-kids(name varchar2(32), regstDt varchar2(6), weight number(4), length number(4));
Create tb-dt(cutDt varchar2(6));

Insert into tb-kids values('jack',null,80,160); ...latest record
Insert into tb-kids values('jack','201106', 70, 150); ...Record in 201106
Insert into tb-kids values ('jack','200909',50, 100); ...Record in 200909

Insert into tb-dt values('201201') ...I expect to see the latest record in the view.

If I change the value in tb-dt,

Update tb-dt set cutDt='201009'; ...I expect to see the record in 201106.

The key is I would like to have a simple and fast view, since there will be very complex join on this view.

Thanks
  • 1. Re: Point of time view with subquery
    rp0428 Guru
    Currently Being Moderated
    Post the queries you have tried so far.
  • 2. Re: Point of time view with subquery
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    Hi,

    I have one table with history-timestamp. I would like to create a view to get the row for a give time . For example:

    Create tb-kids(name varchar2(32), regstDt varchar2(6), weight number(4), length number(4));
    Create tb-dt(cutDt varchar2(6));
    Storing infomation about dates in VARCHAR2 columns is not a really good idea. Use DATE columns instead.
    LENGTH is a bad name for a column; it will get confused with the built-in function also called LENGTH.
    Also, using hyphens in table anmes is a lot of trouble. why not use underscores instead?
    Insert into tb-kids values('jack',null,80,160); ...latest record
    Insert into tb-kids values('jack','201106', 70, 150); ...Record in 201106
    Insert into tb-kids values ('jack','200909',50, 100); ...Record in 200909

    Insert into tb-dt values('201201') ...I expect to see the latest record in the view.
    What if there are 2 (or more) rows in the table with NULL regstDts? Would you want to see them all?
    If I change the value in tb-dt,

    Update tb-dt set cutDt='201009'; ...I expect to see the record in 201106.
    Here's one way:
    CREATE OR REPLACE VIEW        first_kid
    AS
    WITH   got_r_num   AS
    (
         SELECT     k.*
         ,     RANK () OVER (ORDER BY  k.regstdt  NULLS LAST)     AS r_num
         FROM     tb_kids  k
         JOIN     tb_dt      d  ON  d.cutdt  <= NVL (k.regstdt, d.cutdt)
    )
    SELECT     name, regstdt, weight, lngth
    FROM     got_r_num
    WHERE     r_num     = 1
    ;
    The key is I would like to have a simple and fast view, since there will be very complex join on this view.
    It might be faster not to use a view, but to join tb_dt right in the query.
    Using some impossible late date, such as December 31, 9999, instead of NULL in regstdt might make it faster, too.
  • 3. Re: Point of time view with subquery
    942572 Newbie
    Currently Being Moderated
    Thanks!

    Edited by: 939569 on 22-Feb-2013 12:07 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points