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
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.