Solution for displaying 0 when the SQL Division calculation result in DB2 is decimal

In DB2, when the SQL division operation result is decimal, 0 is displayed. In the SQL division operation, the SELECT value is 0 in the DB2 environment. What should I do? This article describes two solutions to the problem 0 when the SQL division operation result in DB2 is a decimal number. SELECT field1/field2 from tb; when the value of field1 is greater than the value of field2, the result of division is <1, that is, 0. xxxxxx at this time, the SELECT value in the DB2 environment is 0. There are two solutions: 1. A parameter of db cfg, MIN_DEC_DIV_3, this parameter is not listed in get db cfg for db, but can be modified. The Database Configuration Parameter MIN_DEC_DIV_3 changes the decimal point of the result of the decimal arithmetic operation. The default value is No, and the decimal point is calculated as 31-p + s-s '. If it is set to Yes, the decimal place is calculated as MAX (3, 31-p + s-S '). This causes the decimal part to always have at least three decimal places. The precision is always 31. This method seems to require DBA to set the database, which is not very good. There is another way to save the country on the curve, debut: Change the SQL statement:

 SQL1 SELECT CAST(field1 AS DOUBLE)/field2 FROM TB;

 

It is to convert field1 to the DOUBLE type first, so that the calculated result will be the decimal point, and 0 will be displayed. xxxxxx next, we need to limit the number of decimal places, so we can use rounding,

 SQL1 SELECT ROUND(CAST(field1 AS DOUBLE)/field2, 2) FROM TB;

 

This means that two decimal places are retained, but if one field of field1 or field2 is NULL, the result is NULL. In this case, perform the following operations according to the logic, if the value of the data column is NULL and it is set to 0, the SQL statement must be written in this way.

 SQL1 SELECT ROUND(COALESCE(CAST(field1 AS DOUBLE), 0)/field2, 2) FROM TB;

 

The usage of COALESCE function system is as follows:. if the input parameter is of the character type and can be NULL, you can use COALESCE (inputParameter, ") to convert NULL to"; B. if the input type is integer and can be null, you can use COALESCE (inputParameter, 0) to convert null to 0; c. if the input parameter is of the character type and is not empty or spaces, you can use COALESCE (inputParameter, ") to convert NULL to", and then determine whether the return value of the function is "; d. the input type IS an integer and IS not NULL. You do not need to use the COALESCE function to directly use is null for non-NULL judgment.