Re: Divide By Zero Error
Huddleston, Joe (huddles@emspo01.hasting.com) wrote:
: just to keep *myself* straight, shouldn't that second where clause be: and
: col2 = 0?
: -joe-
: (huddles@hasting.com)
: ----------
: From: ilist
: To: ddsihck
: Cc: informix-list
: Subject: Re: Divide By Zero Error
: Date: Thursday, August 10, 1995 11:58AM
: >
: > Hi All
: >
: > Does anyone know how to prevent a 'Divide By Zero Error' when executing a
: > SELECT. I'm dividing 2 columns in a computed field. There are occasions
: > when the divisor WILL be zero. When this condition occurs, instead of the
: > 'Divide By Zero Error' occuring I want the result to be zero. I don't
: > want to filter the column containing zeros.
: union two selects:
: SELECT stuff
: ...... col1/col2
: WHERE .....
: AND col2 != 0
: UNION
: SELECT stuff
: ...... 0
: WHERE .....
: AND col2 != 0
Hi !
IMHO, such an UNION is two times slower than original SELECT.
Maybe, it's better to create special PROCEDURE for "strange division"
like this ?
CREATE PROCEDURE mydiv(a ..., b ...) RETURNING ...; IF b = 0 THEN RETURN 0 { or RETURN NULL }; END IF;
RETURN a / b;
END PROCEDURE;
and
SELECT ..., mydiv(col1, col2).
I suppose, it will be a bit faster.
WBR, Vadim J. Nikitin