Archives of the TeradataForum
Message Posted: Thu, 24 Jan 2008 @ 22:32:48 GMT
Does anyone know whether the CAST statement operates differently between version 6.1 and 6.2? The create statement below seems to have worked prior to installation of 6.2 in November, however it does not work now. The issue has to do with converting all single digit months to the month with a 0 in front of it. The table CalendarStuff is used later in the ETL stream in a join statement and has been correctly working since March. I know this because the target tables have been correctly populated since March when the ETL was changed. There is no issue with two digit months so the ETL stream ran fine in November and December under the new version. However once January hit it stopped working since '01' does not equal 1 so the left join to the table produces nulls.
Any help would be appreciated.
create table STAGE.Calendar_Stuff as ( sel monthid, CAST ( EXTRACT(YEAR FROM C.MonthStartDate) AS char(4) ) as year1, CAST (EXTRACT(MONTH FROM C.MonthStartDate) AS char(2)) as month1 from PRODTABLE.CalendarMonth C )with data primary index(Month1,Year1) ;
We were expecting the table to be filled with 2008 and 01 for this month. However the table was filled as follows:
MonthID year1 month1 1121 2008 1 1120 2007 12 1119 2007 11 1118 2007 10 1117 2007 9 1116 2007 8 1115 2007 7 1114 2007 6 1113 2007 5 1112 2007 4 1111 2007 3 . . . . . . . . . 1086 2005 2 CREATE SET TABLE PRODTABLE.CalendarMonthBase ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( MonthID SMALLINT NOT NULL, MonthDesc VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, YearID SMALLINT NOT NULL, QuarterID SMALLINT NOT NULL, MonthStartDate DATE FORMAT 'YY/MM/DD', MonthEndDate DATE FORMAT 'YY/MM/DD', ProcessBatchID INTEGER, ProcessSourceID BYTEINT, FiscalQuarterId INTEGER NOT NULL, FYSortOrder BYTEINT NOT NULL) UNIQUE PRIMARY INDEX ( MonthID );
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|