i have the following data but in query i got the zero problem in division by zero. so i want the output as given below in two forms.
select sqrt(val2*val1)/val1 from t ;
SQL> / ERROR: ORA-01476: divisor is equal to zero
in two forms first query like this
second query like this
3 Answers 3
select sqrt(val2*val1)/val1 from t where val1 != 0 ;
That will avoid the rows where val1 is 0 and so will not cause the «divide by 0» error. This should give you the second format.
For the first format where you want to show the 0 in the output you can use a case statement in the where clause.
Oracle Error Tips by Donald Burleson
Question: I?m using this query and it’s returning a divide by zero error:
SELECT
STUSECMTAMT.totdebit/STUSECMTAMT.totalcr,
STUJOURNAL.cre_bal/(STUSECMTAMT.totdebit/STUSECMTAMT.totalcr)
FROM
STUSECMTAMT,STUDEG,STUJOURNAL
WHERE
STUSECMTAMT.vhno=STUJOURNAL.ref_no
AND
STUSECMTAMT.studeg >
ERROR at line 1:
ORA-01476: divisor is equal to zero
Can anyone shed a bit more light on how to detect and handle a divide by zero error?
Answer: The Oracle oerr utility shows this on the divide by zero ORA-01476 error:
ORA-01476 divisor is equal to zero
Cause: An expression attempted to divide by zero.
Action: Correct the expression, then retry the operation.
STUSECMTAMT.totalcr equates to 0.
You could use a decode or a case to capture the 0 and dtrap the condition without aborting the SQL.
CASE WHEN STUSECMTAMT.totalcr = 0 THEN 0 ELSE STUSECMTAMT.totdebit / STUSECMTAMT.totalcr END
In addition to using DECODE and CASE, another option is to trap the error in PL/SQL with the zero_divide option. Just make a PL/SQL error exception to «trap» the ORA-01476 error with zero_divide
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.put_line(‘Zero divide error — Try again);
END;
Or, you could replace the zero with small value (.000001) and computer the equation:
EXCEPTION
WHEN ZERO_DIVIDE THEN
:divisor_var := .000001;
END;
Lastly, you could replace the output of a divide by zero equation with a zero return value:
EXCEPTION
WHEN ZERO_DIVIDE THEN
return 0;
END;
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .
Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:
and include the URL for the page.
