Solved

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

Posted on 2014-11-29
3
2,822 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
  • 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

821 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