Firebird computed (calculated) field on server side

Question

Newbie in SQL and development in general, I have a table (COUNTRIES) with fields (INDEX, NAME, POPULATION, AREA) Usually i add a client side (Delphi) Calculated field (DENSITY) and OnCalcField :

COUNTRIES.DENSITY=COUNTRIES.POPULATION / COUNTRIES.AREA

Trying to change to Firebird computed field to have all calculation done on server side, i created a field named density and in IBEXPERT "Computed Source" column :

ADD DENSITY COMPUTED BY ((((COUNTRIES.POPULATION/COUNTRIES.AREA))))

Everything work fine but when a Record.AREA = 0 i have a Divided by zero error.

My question is how to avoid this for example with a IF THEN condition to avoid to calculate a field when the divider is 0 or to make the result just =0 in this case.

My environnement : Delphi RIO, Firebird 3.0, IBExpert

Answer 1

You can use IIF(). When the 1st parameter is TRUE, IIF returns value of the second parameter, otherwise of the third parameter.

ADD DENSITY COMPUTED BY (IIF(COUNTRIES.AREA = 0, 0, COUNTRIES.POPULATION / COUNTRIES.AREA))

(note I also removed some extra parenthesis)

When handling division by zero, I recommend returning NULL (instead of zero), with a simple use of NULLIF (internal function which returns null, when both input parameters are equal):

ADD DENSITY COMPUTED BY (COUNTRIES.POPULATION / nullif(COUNTRIES.AREA, 0))

That is: when COUNTRIES.AREA = 0, the whole division operation results in null, too.