3 Replies Latest reply: Feb 22, 2013 2:08 PM by 942572 RSS

    Point of time view with subquery

    942572
      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
          Post the queries you have tried so far.
          • 2. Re: Point of time view with subquery
            Frank Kulash
            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
              Thanks!

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