Five Cats wrote:
> Obnoxio The Clown <obnoxio@hotmail.com> writes
>> Paulo Cooker wrote:
>>> Where can I find information (syntax) about functions that I
>>> can use in SPL? Like, SUBSTR, COALESCE, CURRENTDATE, ROUND,
>>> ...
>>
>> I'd say "the manual", but that's just a guess. I've never actually
>> read one.
>
> I'm sure you are right, and with a bit of effort they can still be
> downloaded from www.ibm.com. Pardon me while I go and wash my mouth
> out. :-P

More precisely, that information is in the Informix Guide to SQL:
Syntax manual, and the starting point for Informix materials can be
http://www.ibm.com/software/data/informix/library (but you can omit
the software/data/ bit and end up at the same point).

Of course, some of the names are spelled differently, too. SUBSTR and
ROUND are spelled like that, but CURRENTDATE is spelled TODAY or
CURRENT YEAR TO DAY depending somewhat on what you want to do next.

Off the top of my head, I don't think there is a direct mapping for
COALESCE; I'd probably write it as:

CREATE FUNCTION coalesce(v1 VARCHAR(255) DEFAULT NULL,
v2 VARCHAR(255) DEFAULT NULL,
vn VARCHAR(255) DEFAULT NULL
) RETURNING VARCHAR(255) AS retval;
IF v1 IS NOT NULL THEN RETURN v1;
ELSIF v2 IS NOT NULL THEN RETURN v2;
ELSE RETURN vn;
END IF;
END FUNCTION;

You would have to generalize this to the maximum number of arguments
you are going to pass to the function. This version is good for any
data type other than CHAR(n) or LVARCHAR(n) where n > 255 - or blobs
in any of their guises.

(Errrm; caution - this understanding of COALESCE is based on a quick
review of the top few items found by Googling "SQL function COALESCE",
and therefore could need to be changed for standards compliance. I
suspect it is more user-friendly than the standardized version.)


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/