NVL2 function does not exist? mysql query

I'm trying to do some queries but I keep getting errors, now I'm thinking that there is something wrong with the mysql installation. Can anybody tell me if there is an error in this query?

         SELECT settings.ID, 
                settings.name, 
                settings.description, 
                NVL2(userSettings.value, userSettings.value, settings.default) 
           FROM settings 
LEFT OUT开发者_开发知识库ER JOIN userSettings ON (settings.ID = userSettings.settingID)

The error I get says the function databaseX.NVL2 does not exist

I recommend staying away from vendor specific functions when a ANSI standard equivalent alternative is available. NVL and IFNULL for example can (often) be replaced with COALESCE.

You can also use CASE WHEN, which means a lot more typing on the downside, but the upside is that people with background in SQL Server for example won't have to deal with Oracles DECODE() or NVL or NVL2, because the logic is right there in the code.

That's probably because NVL2 is an Oracle function, not a MySQL function. I believe the function you are looking for in MySQL would be COALESCE()

As @Eric Petroelje mentioned NVL2() is Oracle function, not MySQL. However MySQL has its own equivalent that can be used in this case: IFNULL():

SELECT ... IFNULL(userSettings.value, settings.default) ...

After several try&error probes, I found this method to emulate Oracle's NVL2 function. It's not very elegant, but it works

SELECT IF(LENGTH(ISNULL(FieldName, '')) > 0, 'Not NULL Value', 'Null Value') FROM TableName

I think this can help you

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

Alternatively you can substitute NVL2 to:

IF (userSettings.value IS NULL, userSettings.value, settings.default)