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:
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:
The next chart shows the 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 Returned
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 ;
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:
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.