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
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.
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.
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.
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.
Use COALESCE
, it's much more simple:
DELIMITER $$
CREATE FUNCTION NVL (first DATETIME, second DATETIME)
RETURNS DATETIME
BEGIN
RETURN COALESCE(first, second);
END
$$
DELIMITER ;
I think the COALESCE() method is what you need.
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With