Solved

Oracle error - ORA-01476: divisor is equal to zero

Posted on 2014-11-29
3
3,318 Views
Last Modified: 2014-11-29
Hi

I'm having a problem with a section of my Query. This is probably because i have NULL fields. So it can't calculate properly.

Because of that, i have this error:

ORA-01476: divisor is equal to zero
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:

How can i fix this so that i just calculate what i can?

Thanks agauin




  case
    when STOP_TYPE = 'DL' and INBOUND = 'N' -- OB POs
    then  TO_CHAR((((TOTAL_MILES - TOTAL_IB_MILES)/TOTAL_MILES) * (ORDER_WEIGHT/TOTAL_OB_WEIGHT)*FREIGHT_COST),'9,999.99')
    when STOP_TYPE = 'DL' and INBOUND = 'I' -- IB POs
    then  TO_CHAR(((TOTAL_IB_MILES/TOTAL_MILES) * (ORDER_WEIGHT/TOTAL_IB_WEIGHT)*FREIGHT_COST),'9,999.99')
    when STOP_TYPE = 'DL' and TOTAL_ELAPSED_TIME < '000:00' -- LTL loads
    then  TO_CHAR(((ORDER_WEIGHT/TOTAL_WEIGHT)*FREIGHT_COST),'9,999.99')
    END AS PO_RATE_PRORATION

Open in new window

0
Comment
Question by:Wilder1626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 40471861
It requires some sub-CASE statements to avoid the divide-by-zero error:
CASE
    WHEN STOP_TYPE = 'DL' AND INBOUND = 'N' THEN -- OB POs
        CASE
            WHEN TOTAL_MILES = 0 OR TOTAL_OB_WEIGHT = 0 THEN 0
            ELSE TO_CHAR((((TOTAL_MILES - TOTAL_IB_MILES)/TOTAL_MILES) * (ORDER_WEIGHT/TOTAL_OB_WEIGHT)*FREIGHT_COST),'9,999.99')
        END
    WHEN STOP_TYPE = 'DL' and INBOUND = 'I' THEN -- IB POs
        CASE
            WHEN TOTAL_MILES = 0 OR TOTAL_IB_WEIGHT = 0 THEN 0
            ELSE TO_CHAR(((TOTAL_IB_MILES/TOTAL_MILES) * (ORDER_WEIGHT/TOTAL_IB_WEIGHT)*FREIGHT_COST),'9,999.99')
        END
    WHEN STOP_TYPE = 'DL' and TOTAL_ELAPSED_TIME < '000:00' THEN -- LTL loads
        CASE
            WHEN TOTAL_WEIGHT = 0 THEN 0
            ELSE TO_CHAR(((ORDER_WEIGHT/TOTAL_WEIGHT)*FREIGHT_COST),'9,999.99')
        END
END AS PO_RATE_PRORATION

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40471867
Hi dsacker

Thanks, this is great. Now it works.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 40471868
Awesome. Glad that helped.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question