Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now