Archives of the TeradataForum

Message Posted: Thu, 24 Jan 2008 @ 22:32:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

Thanks,

Tony

     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 );

	<Prev	Next>		<<First	<Prev	Next>	Last>>