Decimal Datatypes

SAP ASE provides two other exact numeric datatypes, numeric and dec[imal], for numbers that include decimal points. The numeric and decimal datatypes are identical in all respects but one: only numeric datatypes with a scale of 0 and integer datatypes can be used for the IDENTITY column.

Precision and Scale

The numeric and decimal datatypes accept two optional parameters, precision and scale, enclosed in parentheses and separated by a comma:

datatype [(precision [, scale])]
SAP ASE treats each combination of precision and scale as a distinct datatype. For example, numeric(10,0) and numeric(5,0) are two separate datatypes. The precision and scale determine the range of values that can be stored in a decimal or numeric column:
  • The precision specifies the maximum number of decimal digits that can be stored in the column. It includes all digits, both to the right and to the left of the decimal point. You can specify precisions ranging from 1 digit to 38 digits or use the default precision of 18 digits.

  • The scale specifies the maximum number of digits that can be stored to the right of the decimal point. The scale must be less than or equal to the precision. You can specify a scale ranging from 0 digits to 38 digits, or use the default scale of 0 digits.

Storage Size

The storage size for a numeric or decimal column depends on its precision. The minimum storage requirement is 2 bytes for a 1- or 2-digit column. Storage size increases by approximately 1 byte for each additional 2 digits of precision, up to a maximum of 17 bytes.

Use this formula to calculate the exact storage size for a numeric or decimal column:
ceiling (precision / log10(256)) + 1

For example, the storage size for a numeric(18,4) column is 9 bytes.

Decimal Data

Enter decimal and numeric data as a string of digits preceded by an optional plus or minus sign and including an optional decimal point. If the value exceeds either the precision or scale specified for the column, the SAP ASE server returns an error message. Exact numeric types with a scale of 0 are displayed without a decimal point.

This table shows some valid entries for a column with a datatype of numeric(5,3) and indicates how these values are displayed by isql:

Value Entered

Value Eisplayed

12.345

12.345

+12.345

12.345

-12.345

-12.345

12.345000

12.345

12.1

12.100

12

12.000

This table shows some invalid entries for a column with a datatype of numeric(5,3):

Value Entered

Type of Error

1,200

Commas not allowed.

12-

Minus sign should precede digits.

12.345678

Too many nonzero digits to the right of the decimal point.