| Time Dimension [message #93050] |
Fri, 25 October 2002 10:46  |
Kwame Row
Messages: 1
Registered: October 2002
|
Junior Member |
|
|
I have to create and load a time dimension table with data from a range of date. The date range is 01/01/1996 to 12/31/98.
I am trying to manipulate the sysdate ato load the table. I came up with this syntax, but it gives me only one row. He is the table and my syntax. I am thinking of looping with an insert. Is that a good way to do it?
CREATE TABLE TIME
(TIME_KEY NUMBER NOT NULL,
YEAR_KEY NUMBER,
YEAR_ID VARCHAR2 (4),
YEAR_DESC VARCHAR2 (4),
QUARTER_KEY NUMBER,
QUARTER_ID VARCHAR2 (5),
QUARTER_DESC VARCHAR2 (5),
MONTH_KEY NUMBER,
MONTH_ID VARCHAR2 (10),
MONTH_DESC VARCHAR2 (10),
DATE_ID DATE,
DATE_DESC DATE ) ;
======================================================
INSERT INTO TIME
VALUES (custkeyseq.nextval, custkeyseq.nextval,
(SELECT TO_CHAR(SYSDATE+71-2191,'YYYY')YEAR_ID FROM DUAL),
(SELECT TO_CHAR(SYSDATE+71-2191,'YYYY')YEAR_DESC FROM DUAL),
CUSTKEYSEQ.NEXTVAL,
(SELECT TO_CHAR(SYSDATE-2191-100, 'Q')QUARTER_ID FROM DUAL),
(SELECT TO_CHAR(SYSDATE-2191-100, 'Q')QUARTER_DESC FROM DUAL),
custkeyseq.nextval,
(SELECT TO_CHAR(SYSDATE-2191-100, 'MM-YY') MONTH_ID FROM DUAL),
(SELECT TO_CHAR(SYSDATE-2191-100, 'MM-YY') MONTH_DESC FROM DUAL),
(SELECT TO_DATE(SYSDATE-2191-100, 'DD/MM/YY') DATE_ID FROM DUAL),
(SELECT TO_DATE(SYSDATE-2191-100, 'DD/MM/YY') DATE_DESC FROM DUAL))
Please help me.
Thanks.
|
|
|
|
| Re: Time Dimension [message #93080 is a reply to message #93050] |
Mon, 23 December 2002 18:09  |
Reddy Peram
Messages: 52
Registered: December 2002
|
Member |
|
|
Row,
Instead of using a simple sql, why don't you use pl/sql for loading this. Make one variable (equal to sysdate) and put that in loop. Good luck!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
