Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql function to provide equivalent to Oracle NVL

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
like image 479
Stewart Robinson Avatar asked Aug 04 '09 13:08

Stewart Robinson


People also ask

Is there a NVL in MySQL?

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

How do I use NVL in MySQL?

In Oracle, the NVL function allows you to replace NULL with the specified expression i.e. it returns the first operand if it is not NULL, otherwise it returns the second operand. In MySQL you have to use IFNULL function.

How do I replace NVL in SQL?

ISNULL replaced the Oracle NVL function in the SQL server. When an expression in SQL server is NULL, the ISNULL function allows you to return an alternative value for the null.

Is NVL the same as coalesce?

NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences are: NVL accepts only 2 arguments whereas COALESCE can take multiple arguments. NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.


2 Answers

Use COALESCE, it's much more simple:

DELIMITER $$

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

$$

DELIMITER ;
like image 116
Quassnoi Avatar answered Oct 23 '22 15:10

Quassnoi


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

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

like image 30
Cody Caughlan Avatar answered Oct 23 '22 14:10

Cody Caughlan