7.25.3 Notes applying when the data type of the division result is DECIMAL

If the data type of the division result is DECIMAL, the scaling is determined by the value specified for the adb_sql_prep_dec_div_rs_prior operand in the server definition or client definition.

Organization of this subsection

(1) When searching a base table
(2) When searching a viewed table

(1) When searching a base table

When base table T1 that contains the data shown later is searched, the execution result is changed according to the value specified for the adb_sql_prep_dec_div_rs_prior operand.

[Figure]

SELECT statement to be run

SELECT "C1"/"C2" AS "Division Results" FROM "T1"

If INTEGRAL_PART (default) is specified for the adb_sql_prep_dec_div_rs_prior operand

The data type of the division result of "C1"/"C2" will be DECIMAL(38,0) because the integral part takes precedence.

Division result
```
584.
```
If FRACTIONAL_PART is specified for the adb_sql_prep_dec_div_rs_prior operand

The data type of the division result of "C1"/"C2" will be DECIMAL(38,4) because the scaling of the first operand takes precedence.

Division result
```
584.4457
```

To Page Top

(2) When searching a viewed table

If the data type of the result of division (arithmetic operation) specified in the query expression body of the CREATE VIEW statement is DECIMAL, the precision and scaling are determined by the following value: the value of the adb_sql_prep_dec_div_rs_prior operand (specified when the viewed table is searched) in the server definition or client definition.

Example:

Note that this example assumes that the contents of table T1 are as follows.

[Figure]

Definition of the viewed table

CREATE VIEW "VT1"("VC1") AS SELECT "C1"/"C2" FROM "T1"

If the following SELECT statement is run by specifying INTEGRAL_PART for the adb_sql_prep_dec_div_rs_prior operand
```
SELECT "VC1" FROM "VT1"
```
Retrieval results
```
20
```
In this case, data type of column VC1 in viewed table VT1 will be DECIMAL(38,0).
If the following SELECT statement is run by specifying FRACTIONAL_PART for the adb_sql_prep_dec_div_rs_prior operand
```
SELECT "VC1" FROM "VT1"
```
Retrieval results
```
20.2
```
In this case, data type of column VC1 in viewed table VT1 will be DECIMAL(38,1).

Note

The data type determined by the value of the adb_sql_prep_dec_div_rs_prior operand (specified when the viewed table is defined) is stored in the column information of the viewed table stored in table SQL_COLUMNS. Therefore, if different values are specified for the adb_sql_prep_dec_div_rs_prior operand during definition and search of the viewed table, the following two data types might not match:

Data type of the derived columns of the internal derived table that is generated as a result of equivalent exchange of the viewed table
Data type of the columns of the viewed table stored in table SQL_COLUMNS

Note that if both the first and second operands of division are literals, the precision and scaling of the division result are determined by the value of the adb_sql_prep_dec_div_rs_prior operand specified when the viewed table is defined.

To Page Top