Re: Getting data to a file, and divide by zero
In responce to your 1st query regarding returning a columnar formatted
stream of data, one
solution is to execute the UNLOAD TO 'Filename.Ext' SELECT ....., bearing
in mind that Informix
will use the character assigned to the DBDELIMETER environment variable,
which by default is
set to a pipe (|) character, to seperate the columns. Note also that
informix does not enclose
strings in quotation marks.
As regards you second query, our solution was to create a stored
procedure which we then call
as a function in the select statement ( eg: SELECT a, b, divide (a, b)
from ... ) the stored
procedure syntax is as follows :
//***********************************************************************
****************
// Procedure : divide
// Usage : SELECT a, b, divide ( a , b ) ...
// : where d may be zero.
// Description : Check if Divivsor != 0 / NULL
//***********************************************************************
****************
DROP PROCEDURE divide~
CREATE PROCEDURE divide (sp_dividend DECIMAL (13, 5), sp_divisor DECIMAL
(13, 5))
RETURNING DECIMAL (13, 5);
IF ( sp_divisor = 0 ) OR ( sp_divisor IS NULL ) THEN
RETURN 0.0;
END IF
RETURN sp_dividend / sp_divisor;
END PROCEDURE;
Another solution to your devision problem may be to issue two seperate
SQL statements joined
by a union, the first select returning known correct data and the second
known incorrect data,
( eg. SELECT a, b, a / b
FROM ??
WHERE b > 0
UNION
SELECT a, b, 0
FROM ???
WHERE b = 0;)
I hope the above note helps, email me if you have any problems.
Regards.
Chris Jones
ddsiecj@sunny.oed.db.za
De Beers Technical Systems Team