Archives of the TeradataForum

Message Posted: Tue, 07 May 2002 @ 16:05:19 GMT

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

<-- Anonymously Posted: Tuesday, May 07, 2002 11:56 -->

I was told that when doing comparisons, Numeric comparisons are much faster than Character comparisons.

I have an source data that is not normalized (i.e. it has monthly totals contained in same record and only one of these totals pertains to the current reporting period). In later selections we will need the month number, year number, and the monthtotal (based on the report period). The report period is formatted: 'MMMM YYYY' Where MMMM is the month (e.g. January) and YYYY is the year including the century (e.g. 2002). There are approximately 360,000 records in the table in question.

Each input data record is as follows:

StoreNumber, ReportPeriod, JanuaryTotal, FebruaryTotal, MarchTotal, AprilTotal,...,DecemberTotal, YTDTotal

The output data record from this selection is as follows:

StoreNumber, MonthNumber, ReportYear, MonthTotal, YTDTotal

So, if the report period is "March 2002", then the MonthNumber should be 3, the ReportYear should be 2002, and the MonthTotal should be the value in MarchTotal. my Question is this, should I use the calculated MonthNumber in the second case as I do below OR should I use the substring once again and compare to the text strings? OR is there an even better way?

SELECT
    ,StoreNumber
    ,TRIM(BOTH FROM ReportPeriod) as Period
    ,CASE SUBSTR(Period, 1,3)
          WHEN 'JAN' THEN 1
          WHEN 'FEB' THEN 2
          WHEN 'MAR' THEN 3
          WHEN 'APR' THEN 4
          WHEN 'MAY' THEN 5
          WHEN 'JUN' THEN 6
          WHEN 'JUL' THEN 7
          WHEN 'AUG' THEN 8
          WHEN 'SEP' THEN 9
          WHEN 'OCT' THEN 10
          WHEN 'NOV' THEN 11
          WHEN 'DEC' THEN 12
          ELSE 0
     END as MonthNumber
    ,SUBSTR(Period, CHARACTER_LENGTH(Period)-3, 4)
         as ReportYear
    ,CASE MonthNumber
          WHEN  1 THEN JanuaryTotal
          WHEN  2 THEN FebruaryTotal
          WHEN  3 THEN MarchTotal
          WHEN  4 THEN AprilTotal
          WHEN  5 THEN MayTotal
          WHEN  6 THEN JuneTotal
          WHEN  7 THEN JulyTotal
          WHEN  8 THEN AugustTotal
          WHEN  9 THEN SeptemberTotal
          WHEN 10 THEN OctoberTotal
          WHEN 11 THEN NovemberTotal
          WHEN 12 THEN DecemberTotal
          ELSE NULL
      END as MonthTotal
    ,YTDTotal
     FROM ATemp;

Thanks in advance for any advice.

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