Home » Developer & Programmer » Data Integration » Time Dimension
Time Dimension [message #93050] Fri, 25 October 2002 10:46 Go to next message
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 Go to previous message
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!
Previous Topic: Re: Looking for OLAP/Datawarehousing Jobs
Next Topic: EXPRESS 5.0.3 de-support, 6.3 Upgrade
Goto Forum:
  


Current Time: Fri Oct 22 04:37:55 CDT 2021