Insert in to date dimension is not working

Database
Enthusiast

Insert in to date dimension is not working

Hello All,

I have a requirement to create a date and time table using the sys_calendar.calendar; SQL below

CREATE SET TABLE TEST

AS

(

SELECT calendar_date, year_of_calendar,quarter_of_year,month_of_year,week_of_month,day_of_month,day_of_week

From sys_calendar.calendar

Where year_of_calendar >='2000' and year_of_calendar <= '2050'

)

WITH DATA;

And an alter table to insert a new field in to the table

Alter table TEST

ADD Month_Name char(13);

 

And i am trying insert some data into the Month_Name field based on the month_of_year field, Sql below

INSERT INTO TEST

(

Month_Name

)

Select

CASE

WHEN month_of_year = 1 THEN 'Jan'

WHEN month_of_year = 2 THEN 'Feb'

WHEN month_of_year = 3 THEN 'Mar'

WHEN month_of_year = 4 THEN 'Apr'

WHEN month_of_year = 5 THEN 'May'

WHEN month_of_year = 6 THEN 'Jun'

WHEN month_of_year = 7 THEN 'Jul'

WHEN month_of_year = 8 THEN 'Aug'

WHEN month_of_year = 9 THEN 'Sep'

WHEN month_of_year = 10 THEN 'Oct'

WHEN month_of_year = 11 THEN 'Nov'

WHEN month_of_year = 12 THEN 'Dec'

END

FROM sys_calendar.calendar;

 

The insert statement is not working, when i fire the select statemwent on the TEST table Month_Name field is blank (?) even though the insert statement has succesfully ran.

Not sure where i am doing wrong, can someone please guide me here.

 

Many Thanks in advance for the help, by the way i am using version 12 of teradata.

 

Kind Regards

Reddy

 

 

 

2 REPLIES
Junior Contributor

Re: Insert in to date dimension is not working

Hi Reddy,

of course the Insert worked, it added 7 rows for monday to sunday where all other columns are NULL (the Select returned 73414 rows, but your target table is SET so duplicate rows where removed).

You probably want an UPDATE Test SET month_name = ....

And instead of CASE ... you can simply use calendar_date (format 'mmm')  (char(3))

UPDATE Test SET month_name = calendar_date (format 'mmm') (char(3));
Enthusiast

Re: Insert in to date dimension is not working

I have justrealised that i have to use the UPDATE rather than INSERT, which i could manage to work it out.

Many Thanks Dieter nvertheless!!