May 15, 2017 at 2:23 am
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
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
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
Thom A - Monday, May 15, 2017 2:35 AMThe 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)
ENDThis 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