Handling the MONEY Data Type

The MONEY data type stores currency amounts. This data type stores fixed-point decimal numbers up to a maximum of 32 significant digits. You can specify MONEY columns in data definition statements such as CREATE TABLE and ALTER TABLE.

The choice of locale affects monetary data in the following ways:

• The default value of scale in the declaration of MONEY columns
• The currency notation that the client application uses

The locale defines the default scale and currency notation in the MONETARY category of the locale file. For information on the MONETARY category of the locale file, see The MONETARY Category.

Specifying Values for the Scale Parameter

Define a MONEY column with the following syntax.

                 (1)
>>-| Data Type |------------------------------------------------>
 
>--MONEY--+-----------------------------------+----------------><
          |    .-16--------.  .-2--------.    |
          '-(--+-precision-+--+-,--scale-+--)-'
 

1. See IBM Informix: Guide to SQL Syntax.

Element

Purpose

precision

Total number of significant digits in a decimal or money data type

You must specify an integer between 1 and 32, inclusive. The default precision is 16.

scale

Number of digits to the right of the decimal point.

The scale must be an integer between 1 and precision. If you omit the scale, the database server provides a default scale that the database locale defines. For the default locale (U.S. English), the default is 2, as the diagram indicates.

Internally, the database server stores MONEY values as DECIMAL values. The precision parameter defines the total number of significant digits, and the scale parameter defines the total number of digits to the right of the decimal separator. For example, if you define a column as MONEY(8,3), the column can contain a maximum of eight digits, and three of these digits are to the right of the decimal separator. An example of a data value in the column might be 12345.678.

If you omit the scale parameter from the declaration of a MONEY column, the database server provides a scale that the locale defines. For the default locale (U.S. English), the database server uses a default scale of 2. It stores the data type MONEY(precision) in the same internal format as the data type DECIMAL(precision,2). For example, if you define a column as MONEY(10), the database server creates a column with the same format as the data type DECIMAL(10,2). A data value in the column might be 12345678.90.

For nondefault locales, if you omit the scale when you declare a MONEY column, the database server declares a column with the same internal format as DECIMAL data types with a locale-specific default scale. For example, if you define a column as MONEY(10), and the locale defines the default scale as 4, the database server stores the data type of the column in the same format as DECIMAL(10,4). A data value in the column might be 123456.7890.

The GLS code sets for most European languages can support the euro symbol in monetary values. For the complete syntax of the MONEY data type, see the IBM Informix: Guide to SQL Syntax. For a complete description of the MONEY data type, see the IBM Informix: Guide to SQL Reference.

Format of Currency Notation

Client applications format values in MONEY columns with the currency notation that the locale defines. This notation specifies the currency symbol, thousands separator, and decimal separator. For more information about currency notation, see Numeric and Monetary Formats.

In the default locale, the default currency symbol is a dollar sign ($), the default thousands separator is a comma ( , ), and the default decimal separator is a period ( . ) symbol. For nondefault locales, the locale defines the appropriate culture-specific currency notation for monetary values. You can also use the DBMONEY environment variable to customize the currency symbol and decimal separator for monetary values. For more information, see Customizing Monetary Values.