All Forums Database
vidskhare 2 posts Joined 11/12
26 Nov 2012
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;

Qaisar Kiani 337 posts Joined 11/05
28 Nov 2012

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.

You must sign in to leave a comment.