Div By Zero error

[born2program]

I am trying to remedy a Div By Zero error in oracle I am using the following SQL which does not work, any ideas are greatly appreciated.



CASE
((COUNT(DECODE(CONFIG.BCREVIEWSCORE.ANSWER,'1',1,NULL))) / (COUNT(CASE WHEN BCREVIEWSCORE.ANSWER = 1 OR BCREVIEWSCORE.ANSWER = 2 THEN 1 ELSE NULL END))*100)

WHEN 0

THEN 0

ELSE ((COUNT(DECODE(CONFIG.BCREVIEWSCORE.ANSWER,'1',1,NULL))) / (COUNT(CASE WHEN BCREVIEWSCORE.ANSWER = 1 OR BCREVIEWSCORE.ANSWER = 2 THEN 1 ELSE NULL END))*100)

END AS ACCURACY_SCORE

 

[SantaMufasa]

I am using the following SQL which does not work.

One problem that we face here as Tek-Tipsters, new2, is that they don't supply us with a crystal ball to devine whether does not work" means that you are receiving a:


1) a syntax error (it gives you a syntax diagnostic),
2) a run-time error (it blows up while its running), or
3) a logic error (the results don't match your expectations)

Whichever ptoblem it is, could you please copy-and-paste your entire screen, from the beginning of the SELECT to the end of the ensuing error?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[born2program]

I am getting the following error:
ORA-00923: FROM keyword not found where expected
Below is the code. Thanks

SELECT BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE,
COUNT(DISTINCT BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID) TOT_REVIEWS,
COUNT(CASE WHEN BCREVIEWSCORE.ANSWER = 1 OR BCREVIEWSCORE.ANSWER = 2 THEN 1 ELSE NULL END) TOT_QUESTIONS,
COUNT(DECODE(CONFIG.BCREVIEWSCORE.ANSWER,'2',1,NULL)) TOT_INCORRECT,
SUM(CASE WHEN BCREVIEWSCORE.ANSWER = 1 OR BCREVIEWSCORE.ANSWER = 2 THEN BCREVIEWSCORE.WEIGHT ELSE NULL END) WEIGHTED_SCORES,
CASE ((COUNT(DECODE(CONFIG.BCREVIEWSCORE.ANSWER,'1',1,NULL))) / (COUNT(CASE WHEN BCREVIEWSCORE.ANSWER = 1 OR BCREVIEWSCORE.ANSWER = 2 THEN 1 ELSE NULL END))*100)
WHEN 0
THEN 0
ELSE ((COUNT(DECODE(CONFIG.BCREVIEWSCORE.ANSWER,'1',1,NULL))) / (COUNT(CASE WHEN BCREVIEWSCORE.ANSWER = 1 OR BCREVIEWSCORE.ANSWER = 2 THEN 1 ELSE NULL END))*100)
END AS ACCURACY_SCORE
FROM CONFIG.BCEMPLOYEEREVIEW BCEMPLOYEEREVIEW, CONFIG.BCREVIEWSCORE BCREVIEWSCORE
WHERE (BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID =BCREVIEWSCORE.EMPLOYEEREVIEWID)
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE >= TO_DATE('10/01/2006', 'MM/DD/YYYY')
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE <= TO_DATE('10/24/2006', 'MM/DD/YYYY')
GROUP BY BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE

 

[SantaMufasa]

new2WebFocus,

The CASE construct became available in Oracle SELECT statements with Oracle version 8.1.x. Since you are posting this question in the "Oracle 8 and 8i" forum, I thought I had better check first to confirm what version you are running.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[born2program]

Using Oracle 8.0

 

[SantaMufasa]

Sorry, new2WebFocus, CASE doesn't work on Oracle 8.0.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...but, everything you want to do, above, you can do with a DECODE statement. Let me/us know if you need help re-coding your SELECT to work entirely with DECODEs. (It appears that you are already literate with DECODE, however.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[born2program]

Mufasa, Sorry I made an error. We are using Oracle 9i.

 

[SantaMufasa]

Okay, then to identify your problem, I recommend your using a technique called step-wise code isolation. This means, confirm the validity of your code from its most basic up to the final version in this fashion: (Step-wise code add-ins appear in bold.)

(Step 1):
[b]select count(*)
from CONFIG.BCEMPLOYEEREVIEW BCEMPLOYEEREVIEW;[/b]

(Step 2):
[b]select count(*)
from CONFIG.BCREVIEWSCORE BCREVIEWSCORE;[/b]

(Step 3):
select count(*)
from CONFIG.BCEMPLOYEEREVIEW BCEMPLOYEEREVIEW[b]
    ,CONFIG.BCREVIEWSCORE BCREVIEWSCORE
where (BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID =BCREVIEWSCORE.EMPLOYEEREVIEWID)
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE >= TO_DATE('10/01/2006', 'MM/DD/YYYY')
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE <= TO_DATE('10/24/2006', 'MM/DD/YYYY')[/b];

(Step 4):
select count(*)
from CONFIG.BCEMPLOYEEREVIEW BCEMPLOYEEREVIEW;
    ,CONFIG.BCREVIEWSCORE BCREVIEWSCORE
where (BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID =BCREVIEWSCORE.EMPLOYEEREVIEWID)
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE >= TO_DATE('10/01/2006', 'MM/DD/YYYY')
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE <= TO_DATE('10/24/2006', 'MM/DD/YYYY')
[b]GROUP BY BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE[/b];

(Step 5):
select [b]BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE[/b]
from CONFIG.BCEMPLOYEEREVIEW BCEMPLOYEEREVIEW;
    ,CONFIG.BCREVIEWSCORE BCREVIEWSCORE
where (BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID =BCREVIEWSCORE.EMPLOYEEREVIEWID)
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE >= TO_DATE('10/01/2006', 'MM/DD/YYYY')
AND CONFIG.BCEMPLOYEEREVIEW.ENTRYDATE <= TO_DATE('10/24/2006', 'MM/DD/YYYY')
GROUP BY BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE;

Steps 6 through Step n: Continue to add in SELECT expressions until either a) you identify the offending code or b) your code works as you wish/specify.

Let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]