Divide by zero

SSIS and Import/Export
1

Hi,

I am getting a divide by zero error message for the following logic in a derived column.

NotionalAmount / (NumberOfContracts * Multiplier)

how can i get around it it has to be done in a derived column. the values in the data is

NotionalAmount NumberOfContracts Multiplier
-26.35 0 0.2
2

Can you try this, this would give a NULL value in the output.
NotionalAmount /nullif((NumberOfContracts * Multiplier),0)

3

i get an error saying nullif doesn't exist or is incorrect

4

Maybe?:

NotionalAmount / CASE WHEN (NumberOfContracts * Multiplier) = 0 THEN NULL
ELSE (NumberOfContracts * Multiplier) END

5 
CASE WHEN NumberOfContracts = 0 OR Multiplier = 0 THEN 0 
     ELSE NotionalAmount / (NumberOfContracts * Multiplier)
     END
1 Like
6

If this is a derived column transformation in SSIS - then you would need to use a conditional:

(NumberOfContracts == 0) ? 0 : NotionalAmount / (NumberOfContracts * Multiplier)