CODEWITHSUNDEEP
sqloracle-databasecalendaretlbusiness-intelligence
MNS
MNS

Reputation: 395

Filling Time_Dimension?

I wrote this script but it doesn't work. I don't get any error message, it just hangs, could you please help me out?

DECLARE
    vYear_ID VARCHAR(4);
    vQuarter CHARACTER(6);
    vMonth_Num SMALLINT;
    vMonth_Name VARCHAR(20);
    vWeekday_Num SMALLINT;
    vWeekday_Name VARCHAR(20); 
    vStartDate Date := '01/01/1998';
    vEndDate Date := '31/12/2002';
    vDate_ID Date;
    vDate_ID := vStartDate;

BEGIN

    WHILE vDate_ID < vEndDate 
    LOOP
        vYear_ID := TO_CHAR(vDate_ID,'YYYY');
        vQuarter := TO_CHAR(vDate_ID,'YYYY')||'Q'||TO_CHAR(vDate_ID,'Q');
        vMonth_Num := TO_NUMBER(TO_CHAR(vDate_ID,'MM'));
        vMonth_Name := TO_CHAR(vDate_ID,'Month');
        vWeekday_Num := TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(vDate_ID,'D')));
        vWeekday_Name := TO_CHAR(vDate_ID,'Day');

        INSERT INTO Time_Dim VALUES (vDate_ID, vYear_ID, vQuarter, vMonth_Name, vWeekday_Name, vMonth_Num, );

        vDate_ID = to_date(vDate_ID,'DD/MM/YYYY')+1

    END LOOP;

END;

I have to use the While Loop and not a For.

Thanks in advance.

Upvotes: 1

Views: 143

Answers (1)

APC
APC

Reputation: 146269

This doesn't address the exact question. But as the actual question is a dreary "guess the compilation error" I have elected to answer a more interesting question instead :)

INSERT INTO Time_Dim VALUES 
    (vDate_ID, vYear_ID, vQuarter, vMonth_Name, vMonth_Num, vWeekday_Name, vWeekday_Num)
with dt as ( select date '1998-01-01' as start_dt
                    , date '2002-12-31' as end_dt 
             from dual )
     , ths as ( select start_dt + ( level - 1) as id_date
                from dt
                connect by level <= ( end_dt - start_dt ) + 1
              )
select id_date
       , to_char(id_date, 'YYYY')
       , trim(to_char(id_date, 'YYYY'))||'Q'||to_char(id_date, 'Q')
       , to_char(id_date, 'Month')
       , to_number(to_char(id_date, 'MM'))
       , to_char(id_date, 'Day')
       , to_number(to_char(id_date, 'D'))
from ths;

Here's a SQL Fiddle for the SELECT part of the statement.

Upvotes: 2

Related Questions