How to do a NVL (or DECODE or CASE) statement within an Informix SE SQL query ?

Question

Hi,

IBM is very clear on his Informix Standard Engine, it doesn't support neither CASE nor DECODE. "Please upgrade to a new version" : http://www-01.ibm.com/support/docview.wss?rs=632&context=SSGU5Y&dc=DB560&dc=DB520&uid=swg21189712&loc=en_US&cs=UTF-8&lang=en&rss=ct632db2

It will never happen !

So maybe somebody could have an idea. I want to have the equivalent of :

Select NVL(c1, c2) from MyTable

Answer 1

CREATE PROCEDURE NVL(a CHAR(32), b CHAR(32) DEFAULT NULL,
                                 c CHAR(32) DEFAULT NULL,
                                 d CHAR(32) DEFAULT NULL,
                                 e CHAR(32) DEFAULT NULL,
                                 f CHAR(32) DEFAULT NULL,
                                 g CHAR(32) DEFAULT NULL)
   RETURNING CHAR(32);
   IF   a IS NOT NULL THEN RETURN a;
   ELIF b IS NOT NULL THEN RETURN b;
   ELIF c IS NOT NULL THEN RETURN c;
   ELIF d IS NOT NULL THEN RETURN d;
   ELIF e IS NOT NULL THEN RETURN e;
   ELIF f IS NOT NULL THEN RETURN f;
   ELSE                    RETURN g;
   END IF;
END PROCEDURE;

---

Or - less generally:

-- @(#)$Id: nvl_int.spl,v 1.1 1996/08/26 18:33:11 johnl Exp $
--
-- nvl_integer: return v1 if it is not null else return v2

CREATE PROCEDURE nvl_integer(v1 INTEGER, v2 INTEGER DEFAULT 0)
    RETURNING INTEGER;

    DEFINE rv INTEGER;

    IF v1 IS NOT NULL THEN
        LET rv = v1;
    ELSE
        LET rv = v2;
    END IF

    RETURN rv;

END PROCEDURE;

The CHAR version can be used for almost any type (except strings longer than 32, as written) because SE is very good at converting between types. SE does not support explicit casting, either -- it is safe to assume that SE does not have much of SQL past SQL-89.

Answer 2

If you have a look on the IIUG Site in the downloads area, you'll find a collection of SPL routines there to emulate these functions and others. The specific one you're after is the collection called "orclproc".