Search code examples
sqloracle-databasedecodenvl

How to Return a Value using CASE, DECODE, and/or NVL

I'm having a bit of trouble with this. I need a formula that will give me an actual result regardless of whether or not the values are NULL and/or 0. See the following;

SELECT
[...columns...],
(NVL(SUM(table1.qty_rtnd), 0) + NVL(SUM(table1.qty_defective), 0)) / CASE (NVL(table1.sales, 0)) WHEN 0 THEN 1 END AS six_wk_pct_defective,
[...more columns...]

Values in this particular instance:

 table1.qty_rtnd = NULL
 table1.qty_defective = 7
 table1.sales = 560

If the CASE statement is not in this formula and the divisor is 0, Oracle SQL Developer throws an error back to me telling me I cannot divide by zero. That is fine, but when I try to apply a CASE statement to the dividend portion of this formula, the field in the query result is NULL when it should not be (in this particular case, the math makes it that is should be 0.0125).

What am I doing wrong? How can I use CASE, NVL, DECODE or any other functions to fix this issue?

Thanks,

-Ant

UPDATE:

For those looking for an answer. One was provided by someone which is the following;

SELECT (NVL (qty_rtnd, 0) + NVL (qty_defective, 0)) / NVL (NULLIF (sales, 0), 1) FROM table1
Solution

  • How about

    SELECT (NVL (qty_rtnd, 0) + NVL (qty_defective, 0)) / NVL (sales, 1) FROM table1

    or

    SELECT (NVL (qty_rtnd, 0) + NVL (qty_defective, 0)) / NVL (NULLIF (sales, 0), 1) FROM table1

    to safeguard sales=0