Post reply

divide by zero

  • May 15, 2017 at 2:23 am

    #342849

    Hi ,
    This code works fine when I send @exchangeRate !=0 .... but when I send @exchangeRate = 0 ,.. I get divide by zero error from this code.

    my goal is  just insert 0  to RELOCATION_EXP & OTHER_EXP column when I send @exchangeRate = 0 ....no need to divide.

    what changes I require in this code ?

            insert into IS_DRAWING_ATL(    
            DRAWINGID,
            RELOCATION_EXP,
            OTHER_EXP,
            IS_ACTIVE
          )                  
            SELECT
            @drawing_id,
            ROUND(CASE WHEN IDER.RELOCATION_EXP_EX IS NULL OR IDER.RELOCATION_EXP_EX = 0 THEN  CAST((IDG.RELOCATION_EXP*@prev_exchangeRate)AS float)/ CAST(@exchangeRate AS float) ELSE IDG.RELOCATION_EXP END, 9),
            ROUND(CASE WHEN IDER.OTHER_EXP_EX IS NULL OR IDER.OTHER_EXP_EX = 0 THEN  CAST((IDG.OTHER_EXP*@prev_exchangeRate)AS float)/ CAST(@exchangeRate AS float) ELSE IDG.OTHER_EXP END, 9),
            'Y'
            FROM
            IS_DRAWING_ATL IDG LEFT JOIN IS_DRAWING_EX_RATES AS IDER ON IDG.DRAWINGID = IDER.DRAWINGID WHERE IDG.DRAWINGID = @drawing_prev;

  • May 15, 2017 at 2:35 am

    #1942481

    The problem with sending the value @exchangeRate = 0 is because you have the following equation in your SQL:
    CAST((IDG.RELOCATION_EXP*@prev_exchangeRate)AS float)/ CAST(@exchangeRate AS float)
    The part in bold will equate to 0, thus the divisor in your equation is 0 (and ehcen your error).

    Something like this should work:
    CASE @exchangeRate WHEN 0 THEN 0
                             ELSE ROUND(CASE WHEN IDER.RELOCATION_EXP_EX IS NULL OR IDER.RELOCATION_EXP_EX = 0 THEN CAST((IDG.RELOCATION_EXP*@prev_exchangeRate)AS float)/ CAST(@exchangeRate AS float)
                                                                                                               ELSE IDG.RELOCATION_EXP
                                        END, 9)
    END

    This checks for your 0 divider first, and outputs 0 if present, otherwise it does your normal logic.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 15, 2017 at 2:58 am

    #1942485

    Quick suggestion, catch the unwanted values in a NULLIF function, no need to use a CASE statement here.
    😎

    DECLARE @exchangeRate FLOAT = 0;

    ;WITH SAMPLE_DATA AS
    (
      SELECT
       X.Y
       ,X.Z
      FROM (VALUES (10,1),(20,2),(30,3),(40,4),(50,5),(NULL,6),(0,7))X(Y,Z)
    )
    SELECT
      SD.Y
     ,SD.Z
     ,ISNULL(NULLIF(SD.Y,0) / NULLIF(@exchangeRate,0),0)
    FROM SAMPLE_DATA  SD;

  • May 15, 2017 at 3:50 am

    #1942492

    Thom A - Monday, May 15, 2017 2:35 AM

    The problem with sending the value @exchangeRate = 0 is because you have the following equation in your SQL:
    CAST((IDG.RELOCATION_EXP*@prev_exchangeRate)AS float)/ CAST(@exchangeRate AS float)
    The part in bold will equate to 0, thus the divisor in your equation is 0 (and ehcen your error).

    Something like this should work:
    CASE @exchangeRate WHEN 0 THEN 0
                             ELSE ROUND(CASE WHEN IDER.RELOCATION_EXP_EX IS NULL OR IDER.RELOCATION_EXP_EX = 0 THEN CAST((IDG.RELOCATION_EXP*@prev_exchangeRate)AS float)/ CAST(@exchangeRate AS float)
                                                                                                               ELSE IDG.RELOCATION_EXP
                                        END, 9)
    END

    This checks for your 0 divider first, and outputs 0 if present, otherwise it does your normal logic.

    This works beautifully .

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply