DECIMALDIGITS Command | Basic Teradata Query - 17.00 - DECIMALDIGITS - Basic Teradata Query

Basic Teradata® Query Reference

Basic Teradata Query
June 2020
Programming Reference


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.



where the following is true:

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 SQL Engine.

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 Teradata Vantage™ - SQL Functions, 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.


To CAST to DECIMAL(28,28):

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