divide by zero

• spectra

  SSCarpal Tunnel

  Points: 4120

  More actions

  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;

• Thom A

  SSC Guru

  Points: 99143

  More actions

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182951

  More actions

  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;


• spectra

  SSCarpal Tunnel

  Points: 4120

  More actions

  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 .