3

I'm doing some converting from Oracle to MSSQL and I was reading a guide by Oracle on B Supported SQL Syntax and Functions.

I noticed it was stated that there is a NOT NVL function (and its MSSQL equivalent was IS NOT NULL).

I'm compiling a list for my colleagues so we can have a one-stop resource for syntax and supported functions, am I correct in assuming that NOT NVL works like so:

There are 3 columns, name, location, loves_marmite

Andrew | UK | Yes

NOT NVL(loves_marmite, 'Nope')

So the data displayed would be:

Andrew | UK | Nope

I just don't get why it would be listed as an Oracle Function when it's just a logic issue, and what's more is that Oracle has IS NULL and IS NOT NULL.

I'm sorry I'm just looking for some clarification before I pass this document on to my colleagues.

EDIT : If possible would someone have a comprehensive list of function and syntax differences between the two platforms?

ZeroBased_IX
  • 2,667
  • 2
  • 25
  • 46

2 Answers2

4

Check NVL2(param1, param2, param3) function.

If param1 is NOT (NULL or EMPTY STRING) it returns param2 else returns param3.

You could write:

NVL2(loves_marmite, 'Nope', something_else)

Also, see this answer for a list of null-related functions in Oracle

Community
  • 1
  • 1
zendar
  • 13,384
  • 14
  • 59
  • 75
0

First, please see the isNull function. But Oracle may be trying to tell you to replace the NVL funcionality with a case;

SELECT CASE WHEN Foo IS NOT NULL THEN bar
                                 ELSE BLA
             END
borjab
  • 11,149
  • 6
  • 71
  • 98
  • Yes I'm aware of the ISNULL function and the CASE statement but the context of the guide by Oracle would be NOT ISNULL. If that's understandable, I just do not know why it was mentioned. I'm just looking for some clarification for this document I'm creating. – ZeroBased_IX Apr 04 '14 at 09:15
  • I my opinion NOT NVL() is just NOT isNull() and doing not to a string is not what you wanted. In Oracle there is the nvl2 but is SQL there is no IsNotNull as far as I know – borjab Apr 04 '14 at 09:33