0

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
aCarella
  • 2,369
  • 11
  • 52
  • 85
  • You are mixing SUMs and single values in the same query. Secondly, do you have some example values for qty_rtnd, qty_defective and sales? – wolφi Jun 10 '14 at 21:20
  • SUMs don't seem to be a problem when the divisor of this formula is not 0. I updated this post with examples of the column values. – aCarella Jun 10 '14 at 21:27
  • The sample values that you posted do not appear to be consistent with the error that you indicated you were receiving prior to adding the `CASE` statement. If `table1.sales` is 560, there would be no reason for the denominator to have been 0 and thus no reason for a division by 0 error to have been thrown. Also, if you have single values, there doesn't appear to be any reason to use the `SUM` function in your denominator. – Justin Cave Jun 10 '14 at 21:32

2 Answers2

1

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

Roberto Navarro
  • 948
  • 4
  • 16
wolφi
  • 8,091
  • 2
  • 35
  • 64
  • 2
    That would throw an error if `sales` is 0. Not knowing what the actual values are, it's difficult to determine if that is a real problem or not. But it is a potential issue. – Justin Cave Jun 10 '14 at 21:35
  • The second solution you provided me with worked perfectly. Thanks! – aCarella Jun 11 '14 at 13:33
0

It's rather hard to understand what you are asking here since you haven't provided us a test case. You haven't told us, for example, what values the various columns have. You told us that, apparently, the desired result is 0.0125 but without any idea what inputs we're supposed to use, we're a bit blind.

If the only problem is your denominator, I suspect that you want the denominator to be 

CASE WHEN NVL( table1.sales, 0 ) = 0
     THEN 1
     ELSE table1.sales
 END

If that guess is not correct, please help us out by posting a reproducible test case.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384