Equivalent function for oracle NVL in DB2

[Siby Stephen]

Hi Everybody,

We are in the process of converting some oracle
stored procedures to DB2. Oracle NVL function takes two parameters, and
if the first one is NULL, the function will return the second parameter,
otherwise it will return the first one itself. I highly appreciate if
anybody can provide me the equivalent function or a UDF for that
purpose.

Thanks
Stephen

[Greg Nash]

Hi,

VALUE( expr, expr [,expr...] ) returns the first not-null expression from
the list.

--Greg

[David Sharpe]

Hi,
COALESCE() is a synonym for VALUE(). Check out the SQL Refr. for details.

Hope this helps,
David.

[Serge Rielau]

Hi,

The SQL92 compliant function is COALESCE(expr, ...)
If you need a mapping you can create SQL UDF on DB2 UDB V7.1 for
workstations for known argument types and arity. E.g.:

CREATE FUNCTION nvl(exp1 int, exp2 int) RETURNS INT
DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION
RETURN coalesce(expr1, expr2);

Cheers
Serge
--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

[Jean-Marc Blaise]

Hi Stephen

The equivalent is the COALESCE function:

VALUE (coalesce (expr1, expr2, ...)) returns the first not null expression.

Regards,

Jean-Marc

"Siby Stephen" <ste...@ait.com.sg> a écrit dans le message news:
3A6544DB...@ait.com.sg...