Allowing for Divide by zero Error When Creating Computed Columns

1

Hello,

I have a table with many columns including Claims and GEP. I would like to add a computed column called LossRatio which is simply (Claims/GEP)*100. I tried to do this as follows:

ALTER TABLE LossRatios ADD LossRatio AS (Claims/GEP)*100 PERSISTED

But this gave the following error message:

Divide by zero error encountered.
The statement has been terminated.

I then tried to get round this by using CASE WHEN as follows:

ALTER TABLE LossRatios
ADD LossRatio
AS (SELECT CASE WHEN GEP=0 THEN NULL
ELSE((Claims/GEP)*100) END) PERSISTED

This method gave the following error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

I then tried to use NULLIF to get round the problem as follows:

ALTER TABLE LossRatios ADD LossRatio AS NULLIF((Claims/GEP)*100,0) PERSISTED

But this gave the same error message as my first attempt. Is there a way to allow for the divide by zero error when creating computed columns?

Many thanks

Chris

2 
ALTER TABLE LossRatios ADD LossRatio AS (Claims/    NullIf(GEP, 0.0)    )*100 PERSISTED
3

This worked for me:

create table #(Claims int, GEP int)

alter table # add LossRatio AS (NULLIF((Claims/GEP)*100,0)) PERSISTED

You need to wrap the computation in parentheses, IIRC

4

Many thanks that worked perfectly! :slight_smile: