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
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.
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
(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.
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.
-