Hi we're using Oracle 11.1,
I have a query where I'm doing division and taking the Standard Deviation of the Quotient.
My formula looks like this
SELECT
case
WHEN NVL(STRENGTH,0) = 0 THEN 0
ELSE round(STDDEV(STRENGTH_P1/STRENGTH * 100) OVER (PARTITION BY MAIN_QUERY.METRIC),1 )
END STRENGTH,
.
.
.
from ...
Knowing that I'd have denominators that would be zero, I put in safety measures to avoid 'divide by zero' errors.
However when the denominator (STRENGTH) = zero I still get a divide by zero error.
I've commented out this formula from my query and the query runs indicating this is the field that's causing the error.
I've even tried an alternate method using DECODE which is the following:
decode(NVL(STRENGTH,0), 0,0, round( STDDEV(STRENGTH_P1/STRENGTH* 100) OVER (PARTITION BY METRIC),1 ))
I still get the 'divide by zero' error, for some reason, if my STRENGTH variable = 0; then Oracle tells me I'm dividing by zero despite my best efforts to redirect Oracle away from an expression with a denominator that = 0.
I'm 'grouping' by a field called METRIC for this line and so this STDDEV function is reading in several values to process to group together. I'm not sure if that has anything to do with it.
Does anybody have any ideas?
My CASE statement and Decode should work.