Skip to Main Content
  • Questions
  • resoution for ORA-01476: divisor is equal to zero

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: May 21, 2017 - 6:15 am UTC

Answered by: Connor McDonald - Last updated: September 08, 2018 - 10:34 am UTC

Category: Database Development - Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Sir,

could you please provide a solution for the below error.ora 01476 divisor is equal to zero.

Table structure:emp

empno| curr_bal| prev_bal |prev_to_prev_bal
1 | 46 |47 |49
2 |18 |19 |20

select empno,
((curr_bal-prev_bal)/prev_bal) *100 as diff_perc,
((curr_bal-prev_to_prev_bal)/prev_to_prev_bal) *100 as diff_Quar_perc,
from emp;

when i ran the above query i am getting divide by zero error, i tried with Decode and case as well as to_char to resolve it but it is not working

could you please provide the exact query which will resolve Divde by zero error ASAP.

Thanks
Ravi

and we said...


and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

diviser is equal to zero

September 07, 2018 - 5:01 am UTC

Reviewer: Nandan from India

with check_for_row as (
-- if the HWM table does not contain a row with a name
-- that matches the TableName variable, then the session will
-- fail with a zero divisor error. if the name does exist (it should
-- always exist), then the ID will be returned and the query will
-- return the desired data
select case when count(*) = 1 then max(ETL_HWM_ID) else 1/0 end ETL_HWM_ID
from GLOBAL_ETL.ETL_HIGH_WATER_MARK
where ETL_HWM_NAME = upper(trim(''))
)
select ehwm.ETL_HWM_ID
, ehwm.ETL_HWM_LOW_DTM
, ehwm.ETL_HWM_HIGH_DTM
, ehwm.ETL_HWM_INTERVAL
,ehwm.ETL_HWM_INTERVAL_FLG
from ETL_HIGH_WATER_MARK ehwm
, check_for_row cfr
where ehwm.ETL_HWM_ID = cfr.ETL_HWM_ID
and ehwm.ETL_HWM_ACT_FLG = 'N'
Connor McDonald

Followup  

September 08, 2018 - 10:34 am UTC

Is this a question? A statement? A random cut-and-paste from somewhere ?

Here's a random SQL from the AskTOM database.

      select
         ques.fname submitted_name,
         ques.category,
         ques.subject,
         ques.version,
         ques.question question_text,
         ques.answer answer_text,
         ques.admin_id,
         admin.file_content,
         ques.livesql_url,
         ques.status,
         ques.contenthub_content_item_id,
         ques.url_tag,
         qcount.view_count_30days
      from ate_submitted_questions ques
      left join ate_admins admin  on ques.admin_id = admin.id
      left join asktom.ate_question_view_count qcount on ques.id = qcount.question_id
      where ques.id = print_question_answer.p_question_id;


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.