SQL DB2 null calculation causing problems

I have the following SQL:

Select dmvndn "Vendor Number", IFNULL(sum(dmsls) / sum(dmprc), 0) "Calculation"
From MyFile
Group By dmvndn

However, when i run this, i am still getting null records in my "Calculation" field.

I have also tried the COALESCE function, which returns the sam开发者_Go百科e results. I get some records as 0, and some records are blank (or, null).

Both fields are of type P, which i am told is packed numeric.

any ideas or suggestions?

Edit 1 It seems that the problem is not with either of these fields being NULL, it is that one or both fields are 0. And when i divide by zero, i get the empty / blank result.

---

Try

Sum(IFNULL(dmsls,0)) / Sum(IFNULL(dmprc,0))

---

A trick of this kind helps me in MS SQL Server:

Select
  dmvndn "Vendor Number",
  IFNULL(sum(dmsls) / NULLIF(sum(dmprc), 0), 0) "Calculation"
From MyFile
Group By dmvndn

I wonder if it can't help you in DB2.

UPDATE: an explanation.

Basically, it replaces the divisor with NULL if it's 0. And you may probably know that when at least one of the operands is NULL, the result of the operation becomes NULL as well.

To account for the result being NULL you already had your IFNULL on the result. It didn't make much difference then, because none of the operands was likely to be NULL. However, now using IFNULL makes perfect sense.