I have a requirement to create a date and time table using the sys_calendar.calendar; SQL below
CREATE SET TABLE TEST
SELECT calendar_date, year_of_calendar,quarter_of_year,month_of_year,week_of_month,day_of_month,day_of_week
Where year_of_calendar >='2000' and year_of_calendar <= '2050'
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
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'
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.
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));
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!!