Data Conversions Teradata

In order for data to be managed and used, it must have characteristics associated with it. These characteristics are called attributes that include a data type and a length. The values that a column can store are directly related to these two attributes.

There are times when the data type or length defined is not convenient for the use or output display needed. For instance, when character data is too long for display, an option might be to reduce its length. At other times, the defined numeric data type is not sufficient to store the result of a mathematical operation. Therefore, conversion to a larger numeric type may be the only way to successfully complete the request.

When one of these situations interrupt the execution of the SQL, it is necessary to use one or more of the conversion techniques. They are covered here in detail to enhance the understanding and the use of these capabilities.

In normal practices, there should be little need to convert from a number to a character on a regular basis. This requirement is one indicator that the table or column design is questionable. However, if a conversion must be performed, it is much safer to use the ANSI Standard CAST (Convert And Store) function when going from numeric to character instead of the older Teradata implied conversion. Both of these techniques are discussed here.

Conversions should be used only when absolutely necessary because they are intensive on system resources. As an example, I saw an SQL statement that converted four columns six different times. There were around a million rows in the table. The SQL did a lot of processing and it took about one hour to run. By eliminating these 6 million conversions, the SQL ran in under five minutes. Conversions can have an impact, but sometimes you need them. Use them only when absolutely necessary!

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics