Divide by zero


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

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

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


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

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

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

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