Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

810 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