9 Replies Latest reply: Mar 28, 2013 9:05 AM by Frank Kulash RSS

    divisor is equal to zero ?

    889124
      Oracle 11.2.0.2

      I have the following SQL.

      SELECT
      A.Catalog_no Part_No,
      A.Description,
      (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
      A.Invoiced_qty Inv_qty,
      A.Net_curr_amount Total_inv_amount,
      B.Invoice_date Inv_date,
      (A.Invoiced_qty*C.Sale_unit_price) as Sales_Amount,
      C.Order_no CO_number,
      A.Customer_po_no Project_id,
      A.Identity Customer_no,
      A.Name Customer_name,
      B.Currency

      FROM
      TABLEA A,
      TABLEB B,
      TABLE C

      WHERE
      A.Invoice_no=B.Invoice_no
      AND
      A.Order_no=C.Order_no
      AND
      A.Catalog_no=C.Catalog_no
      AND
      A.Series_id <> 'CR'
      AND
      C.Line_state='Invoiced/Closed'
      AND
      B.Invoice_date between to_date('&Date_from', 'DD/MM/YYYY' ) and to_date ('&Date_to', 'DD/MM/YYYY')

      The issue is when I put a date range in. If I enter, (in the date_from) 31/01/2013 and then enter (in the Date_to) 01/02/2013 I get the folllowing error message.

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

      But if I do it the other way round.. it works ? Help !! :)
        • 1. Re: divisor is equal to zero ?
          Centinul
          I typically structure my expressions that have the possibility of a denominator with zero like the following:
          CASE WHEN NVL(denominator,0) = 0
               THEN 0
               ELSE numerator / denominator
          END
          I hope this helps!
          • 2. Re: divisor is equal to zero ?
            jeneesh
            That means you have some records with in A.Invoiced_qty = 0

            When you put other way around (from_date > to_date), no records will be returned, and you wont get any error..

            You can use NULLIF
            A.Net_curr_amount/nullif(A.Invoiced_qty,0)  as Unit_inv_amount,
            Edited by: jeneesh on Mar 28, 2013 6:28 PM
            • 3. Re: divisor is equal to zero ?
              889124
              These are the causing the issue

              (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
              A.Invoiced_qty Inv_qty,
              A.Net_curr_amount Total_inv_amount,
              • 4. Re: divisor is equal to zero ?
                Frank Kulash
                Hi,
                JamesW wrote:
                Oracle 11.2.0.2

                I have the following SQL.

                SELECT
                A.Catalog_no Part_No,
                A.Description,
                (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
                This seems to be the only division taking place, so the error must occur here, when invoiced_qty is 0.
                A.Invoiced_qty Inv_qty,
                A.Net_curr_amount Total_inv_amount,
                B.Invoice_date Inv_date,
                (A.Invoiced_qty*C.Sale_unit_price) as Sales_Amount,
                C.Order_no CO_number,
                A.Customer_po_no Project_id,
                A.Identity Customer_no,
                A.Name Customer_name,
                B.Currency

                FROM
                TABLEA A,
                TABLEB B,
                TABLE C

                WHERE
                A.Invoice_no=B.Invoice_no
                AND
                A.Order_no=C.Order_no
                AND
                A.Catalog_no=C.Catalog_no
                AND
                A.Series_id <> 'CR'
                This site doesn't like to display the &lt;&gt; inequality operator. Use the other (equivalent) inequality operator, !=, when posting here.
                AND
                C.Line_state='Invoiced/Closed'
                AND
                B.Invoice_date between to_date('&Date_from', 'DD/MM/YYYY' ) and to_date ('&Date_to', 'DD/MM/YYYY')

                The issue is when I put a date range in. If I enter, (in the date_from) 31/01/2013 and then enter (in the Date_to) 01/02/2013 I get the folllowing error message.

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

                But if I do it the other way round.. it works ? Help !! :)
                When you say "it works" I assume you mean "It produces no rows, but does not raise an error."
                That's because
                WHERE  x  BETWEEN  y  AND  z
                is exactly equivalent to
                WHERE   x  >=  y  
                AND     y  <=  z
                If y > z, then the condition will always be FALSE, and you will have no opportunity to get the divide-by-0 error.
                Apparantly, you have at least 1 row in that date range where a.invoiced_qty is 0.
                Use
                A.Net_curr_amount / NULLIF (A.Invoiced_qty, 0)    as Unit_inv_amount,
                to get NULL on that row, rather than an error.



                 

                I hope this answers your question.
                If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, so the people who want to help you can re-create the problem and test their ideas. Also post the results you want from that data.
                Explain, using specific examples, how you get those results from that data.
                Simplify the problem as much as possible. Remove all tables and columns that have nothing to do with the problem at hand.
                See the forum FAQ {message:id=9360002}
                • 5. Re: divisor is equal to zero ?
                  889124
                  Yes it does but I these are the columns that are causing the issue

                  (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
                  A.Invoiced_qty Inv_qty,
                  A.Net_curr_amount Total_inv_amount,
                  • 6. Re: divisor is equal to zero ?
                    Frank Kulash
                    Hi,
                    JamesW wrote:
                    Yes it does but I these are the columns that are causing the issue

                    (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
                    A.Invoiced_qty Inv_qty,
                    A.Net_curr_amount Total_inv_amount,
                    Okay; if you can show the problem using just thiose 2 columns from that 1 table, then posting CREATE TABLE and INSERT statements for the sample data, and the results you want from that data, will be easy.

                    I realize that you don't want a run-time error, but you've never said what you want instead. NULLIF is one way to avoid the run-time error. If you don't want what NULLIF gives you, then what do you want? This is one reason why you need to post a little sample data and the results you want from that data.
                    • 7. Re: divisor is equal to zero ?
                      889124
                      Actaully its this statment

                      (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
                      • 8. Re: divisor is equal to zero ?
                        Paul  Horth
                        JamesW wrote:
                        Actaully its this statment

                        (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
                        It's no good posting the same thing. You've already been told that the problem is in the division and that you have a a.invoiced_qty that is zero.

                        Now, what do you want to do about it? What result do you want if it is zero?

                        Or, maybe, you have a data issue: the invoiced_qty should not be zero?
                        • 9. Re: divisor is equal to zero ?
                          Frank Kulash
                          Hi,
                          JamesW wrote:
                          Actaully its this statment

                          (A.Net_curr_amount/A.Invoiced_qty) as Unit_inv_amount,
                          Right, that's the part that's causing the error when invoiced_qty is 0.
                          Now, what results do you want when invoiced_qty is 0?
                          If you want NULL, then use NULLIF, like Jeneesh and I suggested earlier.
                          If you want 0 (or aome other value) then use CASE, as Centinul suggested earlier.
                          If you want something else, say what that is. If you can explain what you want in English, someone will help you code it in SQL.