Using DECODE to avoide DIV by zero errors

[memarkiam]

I'm having trouble with DIV/0 errors. I can't filter this out in the HAVING clause as the whole role row will go, and there is data in other columns I need.

Someone suggested using DECODE, which seemed a good idea, but I can't get the syntax right for this query. The particular statement that is causing the error is:


count(case when (DM2_EMPLOYEE.DATE_OF_TERM between '1-Jan-2001' and '31=Dec-2001') then DM2_EMPLOYEE.DATE_OF_TERM end)/count(case when (DM2_EMPLOYEE.DATE_OF_TERM > '1-Jan-2001' or DM2_EMPLOYEE.DATE_OF_TERM is null) then DM2_EMP_location.EMPLOYEE_NUMBER end) as ChrnLY,


So I tried putting in DECODE thus:
count(case when (DM2_EMPLOYEE.DATE_OF_TERM between '1-Jan-2001' and '31=Dec-2001') then DM2_EMPLOYEE.DATE_OF_TERM end)/count(DECODE(case when (DM2_EMPLOYEE.DATE_OF_TERM > '1-Jan-2001' or DM2_EMPLOYEE.DATE_OF_TERM is null),0,1) then
DM2_EMP_location.EMPLOYEE_NUMBER end) as ChrnLY,

but get a 'missing keyword' error at the ',0,1'

Any ideas?
Any suggestions not using DECODE?
I'm not able to use stored procedures.

Many thanks!!!

Mark
London

 

[sem]

If your COUNT statement produces 0, you should place DECODE OUTSIDE your statement.
decode(count(case when (DM2_EMPLOYEE.DATE_OF_TERM > '1-Jan-2001' or DM2_EMPLOYEE.DATE_OF_TERM is null) then DM2_EMP_location.EMPLOYEE_NUMBER end), 0, 0,
count(case when (DM2_EMPLOYEE.DATE_OF_TERM between '1-Jan-2001' and '31=Dec-2001') then DM2_EMPLOYEE.DATE_OF_TERM end)/count(case when (DM2_EMPLOYEE.DATE_OF_TERM > '1-Jan-2001' or DM2_EMPLOYEE.DATE_OF_TERM is null) then DM2_EMP_location.EMPLOYEE_NUMBER end)