Implied CAST Teradata

Compatibility: Teradata Extension

Although the CAST function is the ANSI standard, it has not always been that way. Prior to the CAST function, Teradata had the ability to convert data from one type to another.

This conversion is requested by placing the "implied' data type conversion in parentheses after the column name. Therefore, it becomes a part of the select list and the column request. The new data type is written as an attribute for the column name.

The following is the format for requesting a conversion:

At first glance, this appears to be the best and shortest technique for doing conversions. However, there is a hidden danger here when converting from numeric to character that is demonstrated in this SELECT that uses the same data as above to do implied CAST conversions:

1 Row ReturnedImplied CAST

What happened in the column named OK and N_OK?

The answer to this question is: the value 128 is 1 greater than 127 and therefore too large of a value to store in a BYTEINT. So it is automatically stored as a SMALLINT (5 digits plus a sign) before the conversion. The implicit conversion changes it to a character type with the first 3 characters being returned. As a result, only the first 3 spaces are seen in the report (_ _ _ 128). Likewise, N_OK is stored as (_ _ -128) with the first three characters (2 spaces and - ) shown in the output.

Always think about the impact of the sign as a valid part of the data when converting from numeric to character. As mentioned earlier, if you find that conversions of this type are regularly necessary, the table design needs to be re-examined.

As demonstrated in the above output, it is always safer to use CAST when going from character to numeric data types

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

Teradata Topics