mysql function to provide equivalent to Oracle NVL

Question

I have a strange situation. I have to use NVL(columna, columnb) in Oracle and MySQL. I can't change the SQL as it is in a package I can't edit but it is the only thing that doesn't work in the application I have between MySQL and Oracle.

How would I write NVL() in MySQL. I've looked here (http://dev.mysql.com/doc/refman/5.0/en/create-function-udf.html) and it looks like I have to write it in C and link it to MySQL.

However http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html seems to say I can do it without compiling an add on.

I've tried this but it doesn't work. What am I doing wrong?

CREATE FUNCTION NVL (first DATETIME, second DATETIME)
RETURNS DATETIME
begin
DECLARE first DATETIME;
DECLARE second DATETIME;
DECLARE return_value DATETIME;
SET return_value = IFNULL(first,second);
RETURN return_value;
end

Answer 1

Use COALESCE, it's much more simple:

DELIMITER $$

CREATE FUNCTION NVL (first DATETIME, second DATETIME)
RETURNS DATETIME
BEGIN
        RETURN COALESCE(first, second);
END

$$

DELIMITER ;

Answer 2

I think the COALESCE() method is what you need.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce