[George Williams Contributions: 0Level 1☆]

I am trying to create a growth rate search using a formula (present-past)/past. i was successful in using a decode function to get the result for past and present, but when i try to preform the final calculation i am getting a divide by zero error. here is the formula

(CASE TO_CHAR({trandate},'YYYY') WHEN '2017' THEN decode(to_char({trandate},'YYYY'),2017,{quantity}, 0) ELSE 0 end – CASE TO_CHAR({trandate},'YYYY') WHEN '2018' THEN decode(to_char({trandate},'YYYY'),2018,{quantity}, 0) ELSE 0 END)/CASE TO_CHAR({trandate},'YYYY') WHEN '2018' THEN decode(to_char({trandate},'YYYY'),2018,{quantity}, 0) ELSE 0 END

i am able to get results for the subtraction part, only get the error when i try to divide

Any help is appreciated.
This is a cached copy. Click here to see the original post.

[Olivier Gagnon NC Contributions: 0Level 1☆]

wrap your last case statement in: nullif(CASE TO_CHAR({trandate},'YYYY') WHEN '2018' THEN decode(to_char({trandate},'YYYY'),2018,{quantity}, 0) ELSE 0 END,0)

[Author]

Posts