This content has been marked as final. Show 3 replies
939569 wrote:Storing infomation about dates in VARCHAR2 columns is not a really good idea. Use DATE columns instead.
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));
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 recordWhat if there are 2 (or more) rows in the table with NULL regstDts? Would you want to see them all?
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,Here's one way:
Update tb-dt set cutDt='201009'; ...I expect to see the record in 201106.
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.