Archives of the TeradataForum
Message Posted: Tue, 07 May 2002 @ 16:05:19 GMT
<-- 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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|