Specifying Column Format
You may change the format, defined in the Teradata SQL CREATE TABLE statement, of data displayed in a result using the FORMAT phrase. For example, to prefix each salary summary with a dollar sign, change your report statement as follows:
SELECT DeptNo, Name, Salary FROM Employee
WHERE DeptNo IN (100, 700)
WITH SUM(Salary) (FORMAT ’$$$$,$$9.99’) BY DeptNo
WITH SUM(Salary) (FORMAT ’$$$$,$$9.99’)
ORDER BY Name;
The FORMAT phrase, enclosed by parentheses, immediately follows the summary definition. The format string itself is enclosed by apostrophes.
Your result now looks like this (Figure 13):
Figure 13: Column Format Example
Placed immediately following a column name, a FORMAT phrase changes the format of all data in the column. For example, in the statement:
SELECT Name, Salary (FORMAT ’$$$$$9’) FROM Employee
WHERE DeptNo = 600 AND Salary/12 < 2500;
the phrase FORMAT ‘$$$$$9’ eliminates the comma and decimal places defined for the Salary column (refer to the Teradata SQL CREATE TABLE statement in SQL Data Definition Language), and specifies an initial dollar sign for each value in the Salary column. For example:
Name Salary
------------ ----------
Kemper R $29000
Newman P $28600
Table 10 lists the characters that you may use in a FORMAT phrase and explains their use. A FORMAT phrase cannot exceed 18 digit positions (17 if the phrase contains the E character).
|
Character |
Meaning |
|
/ : % |
Insertion characters. Copied to output string where they appear in the FORMAT phrase. |
|
, |
Insertion character. The comma is only inserted if a digit has already appeared. |
|
. |
A special insertion character in that it represents a decimal point position. |
|
B |
Insertion character. A blank is copied to the output string wherever a B appears in the FORMAT phrase. |
|
+ - |
Sign characters. May be placed at the beginning or end of a format string. One sign character places the edit character in a fixed position for the output string. When two or more of these characters are present on the left, the sign floats (moves to the position just to the left of the number as determined by the stated structure). The + translates to + or - as appropriate; the - translates to - or blank. |
|
$ |
Dollar sign. One $ places the edit character in a fixed position for the output string. When a result is formatted using a single $ with Z’s for zero-suppressed decimal digits (for example, $ZZ9.99), blanks may occur between the $ and the leftmost non-zero digit of the number. When two or more $’s are present, the $ floats to the right, leaving no blanks between it and the leftmost digit. If + or - is present, the dollar sign cannot precede it. |
|
V |
Implied decimal point position. Internally, the V is recognized as a decimal point to align the numeric value properly for calculation. Because the decimal point is implied, it does not occupy any space in storage and is not included in the output. |
|
Z |
Zero-suppressed decimal digit. Translates to blank if the digit is zero and preceding digits are also zero. When only Z’s, commas, and dots are used, the FORMAT phrase means “blank when zero.” For example, ZZZZZ, ZZ.Z, and Z,ZZZ.ZZ print only blanks if the number is zero. A Z is illegal if it is specified following a 9. |
|
9 |
Decimal digit (no zero suppress). |
|
E |
For exponential notation. Defines the end of the mantissa and the start of the exponent. |
|
char(n) |
For more than one occurrence of the following characters: -, +, $, Z, or 9. The (n) notation means that the character is to be repeated n number of times. |
|
- |
Dash character. Used when storing numbers such as telephone numbers, social security numbers, and account numbers. If a dash appears immediately after the first digit or before the last digit, it is taken as an embedded dash rather than a sign character. A dash is illegal if it follows a period, comma, plus sign, dollar sign, or V. |
|
S |
Signed zoned decimal character. Defines signed zoned decimal input as a numeric data type and displays numeric output as signed zone decimal character strings. The S must follow the last decimal digit in the FORMAT phrase. It cannot be used in the same phrase with the characters: %, +, $, Z, or E. |
The display results of various FORMAT phrases for numeric data are shown in Table 11. (For information on how to use the Signed Zoned Decimal format, see SQL Data Types and Literals [B035-1143].) Note that the Teradata Database System normally rounds up a decimal result when the trailing digit is 5 or more. However, when a decimal number is represented as an integer, a fraction that is exactly 5 or less is truncated.
|
Teradata SQL FORMAT Phrase |
Data |
Result |
|
(FORMAT ‘$$9.99’) |
.069 |
$0.07 |
|
(FORMAT ‘999V99’) |
128.457 |
128.46 |
|
(FORMAT ‘$$9.99’) |
1095 |
****** |
|
(FORMAT ‘ZZ,ZZ9.99’) |
1095 |
1,095.00 |
|
(FORMAT ‘9.99E99’) |
1095 |
1.09E03 |
|
(FORMAT ‘$(5).9(2)’) |
1 |
$1.00 |
|
(FORMAT ‘999-9999’) |
8278777 |
827-8777 |
|
(FORMAT ‘ZZ,ZZ9.99-’) |
1095 |
1,095.00- |
|
(FORMAT ‘99.9’) |
30.455 |
30.5 |
|
(FORMAT ‘99.9’) |
30.500 |
30.5 |
|
(FORMAT ‘99.9’) |
30.543 |
30.5 |
|
(FORMAT ‘99.9’) |
30.565 |
30.6 |
|
(FORMAT ‘99’) |
30.455 |
30 |
|
(FORMAT ‘99’) |
30.500 |
30 |
|
(FORMAT ‘99’) |
30.543 |
31 |
|
(FORMAT ‘99’) |
30.565 |
31 |