Archives of the TeradataForum

Message Posted: Fri, 25 Jan 2008 @ 17:43:25 GMT

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

Thanks to all that applied. The mystery has been solved. It turns out that it was a case statement earlier in the ETL stream that would only work as intended in January.

     ,CASE WHEN A.PAY_PERIOD_MONTH = ' ' THEN NULL
     WHEN A.PAY_PERIOD_MONTH < '10' THEN '0'||TRIM(A.PAY_PERIOD_MONTH)
                  ELSE A.PAY_PERIOD_MONTH
     END AS PAY_PERIOD_MONTH
     ;

PAY_PERIOD_MONTH is defined as char(2) and would come in from the source as '1 ' for January, '2 ' for February, etc. So the only time the "THEN" is executed is in January since '1 ' is less than '10'. However '2 ' is not less than '10' so it would produce the '2 ' which would indeed join to the CalendarStuff table detailed below. However there is no match for '01' in the table so thus it did not find the row this month. Apparently the code was not tested for all months.

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