1.2.4 Notes on using the decimal type

The following signs can be used in the decimal, date interval, and time interval types:

Sign valueMeaning
X'A'Not recognized as a sign (error)*
X'B'Not recognized as a sign (error)*
X'C'Recognized as the positive sign
X'D'Recognized as the negative sign
X'E'Not recognized as a sign (error)*
X'F'Recognized as the positive sign

* This is recognized as a sign when Y is specified in the pd_dec_sign_normalize operand of the system definition. For details about the pd_dec_sign_normalize operand, see the manual HiRDB Version 8 System Definition.

When N is specified in the pd_dec_sign_normalize operand or this operand is omitted, the signs of stored data are not normalized. In such a case, the signs of stored data items may contain X'C' and X'F', both of which are recognized as the positive sign. Signs may also change during the type conversion and operation processes at the time of SQL statement execution.

To standardize the signs in a database that contains mixed sign codes, it may be necessary to reload the table data.

For details about the facility for conversion to a decimal signed normalized number, see the explanation of the facility in the HiRDB Version 8 System Operation Guide.

If the data stored in a decimal-type column is positive but the signs of the data are not standardized to X'C', column retrieval operations may yield a discrepancy between the signs of the data and those of retrieval results. When you create a UAP with sign codes in mind, you need to take the following items into consideration:

Organization of this subsection
(1) Signs in retrieval results for a decimal-type column defining an index
(2) Signs in retrieval results when the retrieval (in which a GROUP BY clause is specified) is performed by specifying a decimal-type grouping column in a selection expression
(3) Signs in retrieval results when an SQL extension optimizing option is specified

(1) Signs in retrieval results for a decimal-type column defining an index

When a decimal-type column for which an index is defined is retrieved, signs in the retrieval results are handled as follows:

(2) Signs in retrieval results when the retrieval (in which a GROUP BY clause is specified) is performed by specifying a decimal-type grouping column in a selection expression

When a decimal-type grouping column is specified in a selection expression for a retrieval for which a GROUP BY clause is specified, signs in the retrieval results are handled as follows:

(3) Signs in retrieval results when an SQL extension optimizing option is specified

When the hash join, hash execution of a subquery is specified as an SQL extension optimizing option, a retrieval specifying either queries or subqueries in which multiple tables are joined by = will result in the following signs: