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

Source: Internet
Author: User

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:



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,


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.


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.