Stored PRocedure to Load Date Dim

Database

Stored PRocedure to Load Date Dim

I have written a Teradata stored procedure to load my date dim for 50 years. when I am compiling the procedure I am getting an error msg(SPL1028:E(L64), Illegal attempt to modify symbol 'In_Start_Date'.) Plz help.

CREATE PROCEDURE Populate_Date_Dim (IN In_Start_Date date,IN In_End_Date date)

START_LOOP:

BEGIN

DECLARE SK_COUNTER INTEGER DEFAULT 0;

DATE_INCREMENT_LOOP:

WHILE In_Start_Date <= In_End_Date

DO

INSERT INTO date_table ( DATE_SK,

DATE_VALUE, DAY_VALUE, WEEK_NUMBER_IN_YEAR, MONTH_NUMBER,

MONTH_NAME, QUARTER, QUARTER_NUMER,

HALF, YEAR_NUM, BANK_HOLIDAY,DW_LOAD_TS,DW_UPDATE_TS

)

SELECT DATE_SK, DATE_VALUE, DAY_VALUE,

WEEK_NUMBER_IN_YEAR, MONTH_NUMBER,

MONTH_NAME, QUARTER, QUARTER_NUMER,

HALF, YEAR_NUM, BANK_HOLIDAY,DW_LOAD_TS,DW_UPDATE_TS

FROM

(

SELECT

:SK_COUNTER AS DATE_SK ,

CALENDAR_DATE AS DATE_VALUE,

DAY_OF_YEAR AS DAY_VALUE,

WEEK_OF_YEAR AS WEEK_NUMBER_IN_YEAR,

MONTH_OF_YEAR AS MONTH_NUMBER,

CASE

WHEN MONTH_OF_YEAR = 01 THEN 'JANUARY'

WHEN MONTH_OF_YEAR = 02 THEN 'FEBRUARY'

WHEN MONTH_OF_YEAR = 03 THEN 'MARCH'

WHEN MONTH_OF_YEAR = 04 THEN 'APRIL'

WHEN MONTH_OF_YEAR = 05 THEN 'MAY'

WHEN MONTH_OF_YEAR = 06 THEN 'JUNE'

WHEN MONTH_OF_YEAR = 07 THEN 'JULY'

WHEN MONTH_OF_YEAR = 08 THEN 'AUGUST'

WHEN MONTH_OF_YEAR = 09 THEN 'SEPTEMBER'

WHEN MONTH_OF_YEAR = 10 THEN 'OCTOBER'

WHEN MONTH_OF_YEAR = 11 THEN 'NOVEMBER'

WHEN MONTH_OF_YEAR = 12 THEN 'DECEMBER'

END AS MONTH_NAME,

CASE

WHEN QUARTER_OF_YEAR = 1 THEN 'Q1'

WHEN QUARTER_OF_YEAR = 2 THEN 'Q2'

WHEN QUARTER_OF_YEAR = 3 THEN 'Q3'

WHEN QUARTER_OF_YEAR = 4 THEN 'Q4'

END AS QUARTER,

QUARTER_OF_YEAR AS QUARTER_NUMER,

CASE

WHEN MONTH_OF_YEAR IN (01,02,03,04,05,06) THEN 'HALF1'

WHEN MONTH_OF_YEAR IN (07,

08,09,10,11,12) THEN 'HALF2'

END AS HALF ,

YEAR_OF_CALENDAR AS YEAR_NUM,

'N' AS BANK_HOLIDAY,

cast(current_date as date format 'YYYY-MM-DD') AS DW_LOAD_TS,

cast(current_date as date format 'YYYY-MM-DD') AS DW_UPDATE_TS

from SYS_CALENDAR.CALENDAR

WHERE CALENDAR_DATE = :In_Start_Date ) AS INTERMIDIATE_DATE_DIM ;

SET In_Start_Date = cast(current_date as date format 'YYYY-MM-DD') + 1;

SET SK_COUNTER = SK_COUNTER +1;

END WHILE;

END START_LOOP;

1 REPLY
Enthusiast

Re: Stored PRocedure to Load Date Dim

IN_START_DATE is your IN parameter and you are attempting to modify the IN parameter by

SET In_Start_Date = cast(current_date as date format 'YYYY-MM-DD') + 1;

You can't update the input parameter of a stored procedure.

Either define this parameter as INOUT or use a local date variable instead.