To convert a numeric type value to a character string, the character description must contain a data type declaration. A FORMAT phrase, by itself, cannot be used to convert a numeric type value to a character type value. The phrase only controls how to display the resultant value.
If the character description does not include a FORMAT phrase, then the format of the original numeric value determines how to display the data.
The Teradata conversion syntax form of numeric-to-character conversion uses explicit or default FORMATs to convert to a character representation. It then truncates or extends with pad characters, depending what length the character string dictates. This can lead to a loss of significance.
Attempting to convert from a numeric type to a character type that uses a GRAPHIC server character set generates an error.
As a general rule, you should store numbers as numeric data, not as character data. For example, a table is created with the following code:
CREATE TABLE job AS (job_code CHAR(6) PRIMARY KEY ,description CHAR(70) );
Subsequently, the following query is made:
SELECT job_code, description FROM job WHERE job_code = 1234;
The problem here is that ‘1234’, ‘1234’, ‘01234’, ‘001234’, ‘+1234’, and so on, are all valid character representations of the numeric literal value, and the system cannot tell which value to use for hashing. Therefore, the system must do a full table scan to convert all job_code values to their numeric equivalents so that it can do the comparisons.