DECIMALDIGITS

Basic Teradata Query Reference

brand
Teradata Tools and Utilities
prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

This control enables users to specify what precision the database should use for decimal values associated with subsequently issued SQL requests for non-Field Mode responses. It overrides the precision specified by a CLI System Parameter Block (SPB) for max_decimal_returned entry.

The SHOW CONTROLS command can be used to determine the current DECIMALDIGITS setting.

If a max_decimal_returned entry has been established, the initial value for the setting is based on the SPB entry's value.

Syntax



where the following is true:

n
The range for n is 0...255 inclusive. DEFAULT can be used to indicate that the setting should be reset back to its initial value.

Using a 0 value, or no value at all, is equivalent to using DEFAULT.

Non-0 integer values will be subject to further validation by the database server.

DEFAULT
DEFAULT can be used to indicate that the setting should be reset back to its initial value.

Usage Notes

Using the DECIMALDIGITS command prior to establishing sessions is valid. The current DECIMALDIGITS setting is not used when the database version being used does not support the Max Decimal Precision feature.

The current DECIMALDIGITS value remains in effect for all subsequent requests until explicitly changed, and is persistent across LOGONs done within the same BTEQ invocation.

The maximum decimal digits to return applies to all record modes (Record, Indicator, and Multipart Indicator), but does not apply to Field Mode. In Field Mode, a CAST must be performed or the FORMAT clause must be used.

The difference in behavior between Field and either Record or Indicator Modes occurs because Field Mode deals with formatted data, where either FORMAT or CAST can be easily used. In Record or Indicator Mode, the data is in internal representation, such as, packed decimal for DECIMAL data returned to the mainframe, where compatibility is important.

The Indicator Mode behavior with .SET DECIMALDIGITS n is the same as doing an explicit CAST. See SQL Functions, Operators, Expressions, and Predicates (B035-1145) for more information on truncation and rounding.

The DECIMALDIGITS setting has no effect on Field Mode behavior. To get the same effect in Field Mode as in Indicator Mode with .SET DECIMALDIGITS n, perform either a CAST to DECIMAL(n,n), or perform a FORMAT clause with n digits specified.

Example – DECIMALDIGITS

To CAST to DECIMAL(28,28):

   .logon dbs5/dbc,dbc
   select * from dbc.dbcinfo;
select cast(0.000000000100000000020000000003 as DECIMAL(28,28));
.logoff