Formatted Data Teradata

Compatibility: Teradata Extension

Remember that truncation works in Teradata mode, but not in ANSI mode. So, another way to make data appear to be truncated is to use the Teradata FORMAT in the SELECT list with one or more columns when using a tool like BTEQ. Since FORMAT does not truncate data, it works in ANSI mode.

The syntax for using FORMAT is:

The next SELECT demonstrates the use of FORMAT:

1 Row ReturnedFormatted Data

There are a couple of things to notice in this output. First, it works in ANSI mode because truncation does not occur. The distinction is that all of the data from the column is in spool. It is only the output that is shortened, not truncated. The character data types use the ‘X’ for the formatting character.

Second, formatting does not round a data value as with the 121.53, the display is shortened. The numeric data types use a ‘9’ as the basic formatting character. Others are shown in this.

Next, DATE type data uses the ‘M’ for month, the ‘D’ for day of the month and ‘Y’ for the year portion of a valid date. Lastly, the case of the formatting characters does not matter. The formatting characters can be written in all uppercase, lowercase, or a mixture of both cases.

The two following charts show the valid formatting characters for Teradata and provide an explanation of the impact each one has on the output display when using BTEQ:

valid formatting characters for Teradata

valid formatting characters for Teradata

The next chart shows the formatting characters used in conjunction with DATE data:

formatting characters used in conjunction with DATE data

There is additional information on date formatting in a later dedicated exclusively to date processing.

The next SELECT demonstrates some of the additional formatting symbols:

1 Row ReturnedSELECT demonstrates some of the additional formatting symbols

There are only two things that need to be watched when using the FORMAT function. First, the data type must match the formatting character used or a syntax error is returned. So, if the data is numeric, use a numeric formatting character and the same condition for character data. The other concern is configuring the format mask big enough for the largest data column. If the mask is too short, the SQL command executes, however, the output contains a series of ************* to indicate a format overflow, as demonstrated by the following SELECT:

SELECT 2014859999 (FORMAT '999-9999') AS Fmt_Phone ;
1 Row Returned



All of these FORMAT requests work wonderfully if the client software is BTEQ. After all, it is a report writer and these are report writer options. The issue is that the ODBC and Queryman look at the data as data, not as a report. Since many of the formatting symbols are "characters" they cannot be numeric. Therefore, the ODBC strips off the symbols and presents the numeric data to the client software for display.

Tricking the ODBC to Allow Formatted Data

If a tool uses the ODBC, the FORMAT in the SELECT is ignored and the data comes back as data, not as a formatted field. This is especially noticeable with numeric data and dates.

To force tools like Queryman to format the data, the software must be tricked into thinking the data is character type, which it leaves alone. This can be done using the CAST function.

The next SELECT uses the CAST operation to trick the software into thinking the formatted data is character:

1 Row ReturnedTricking the ODBC to Allow Formatted Data

Do not let the presence of AS in the above SELECT confuse you. The first AS, inside the parentheses, goes with the new data type for the CAST. Notice that the parentheses enclose both the data and the FORMAT so that they are treated as a single entity. The second AS is outside the parentheses and is used to name the alias.

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

Teradata Topics