20

Is there a performant equivalent to the isnull function for DB2?

Imagine some of our products are internal, so they don't have names:

Select product.id, isnull(product.name, "Internal) 
From product

Might return:

1 Socks 
2 Shoes 
3 Internal 
4 Pants
Dave
  • 917
  • 1
  • 8
  • 20

8 Answers8

42

For what its worth, COALESCE is similiar but 

IFNULL(expr1, default)

is the exact match you're looking for in DB2.

COALESCE allows multiple arguments, returning the first NON NULL expression, whereas IFNULL only permits the expression and the default.

Thus

SELECT product.ID, IFNULL(product.Name, "Internal") AS ProductName
FROM Product

Gives you what you're looking for as well as the previous answers, just adding for completeness.

MadMurf
  • 2,247
  • 3
  • 23
  • 30
  • 2
    +1 for giving an *exact* match for the IsNull statement. I voted up the accepted answer as well, but I'm using yours instead. THANKS! – David Oct 03 '11 at 18:37
5

In DB2 there is a function NVL(field, value if null).

Example:

SELECT ID, NVL(NAME, "Internal) AS NAME, NVL(PRICE,0) AS PRICE FROM PRODUCT WITH UR;

Md. Kamruzzaman
  • 1,895
  • 16
  • 26
3
Select Product.ID, VALUE(product.Name, "Internal") AS ProductName from Product
Jnn
  • 140
  • 4
  • 2
    Er, yes. However, note that `VALUE(...)` [is a synonym for `COALESCE(...)`](http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0000868.htm); the optimizer is probably swapping out the call for you. Personally, I recommend avoiding non-ANSI semantics where possible (that is, use `COALESCE(...)` instead). – Clockwork-Muse May 27 '14 at 11:41
3

I'm not familiar with DB2, but have you tried COALESCE?

ie:


SELECT Product.ID, COALESCE(product.Name, "Internal") AS ProductName
FROM Product
Chris Shaffer
  • 32,199
  • 5
  • 49
  • 61
0

COALESCE function same ISNULL function Note. you must use COALESCE function with same data type of column that you check is null.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Fuangwith S.
  • 5,654
  • 8
  • 37
  • 41
0

I think COALESCE function partially similar to the isnull, but try it.

Why don't you go for null handling functions through application programs, it is better alternative.

Taryn
  • 242,637
  • 56
  • 362
  • 405
venkatram
  • 39
  • 1
  • 2
  • COALESCE is different in that you can feed it a variable number of parameters and it will return the first non-null, with an optional default. So if I have varA, varB and VarC any of which might be null (say they come from different tables in an outer join) and if all are null I want an empty string, I could use COALESCE(varA,varB,varC,'') and get the first of them that wasn't null, or an empty string if they are all null. IFNULL (ISNULL in T-SQL) is an all or nothing. – Joey Morgan Aug 04 '16 at 20:06
0

hope this might help someone else out there

  SELECT 
.... FROM XXX XX
WHERE
....
AND(
       param1 IS NULL
       OR XX.param1 = param1
       )
Procrastinator
  • 2,526
  • 30
  • 27
  • 36
turnmoil
  • 9
  • 2
0

Another option, in case you need to use if/else, is:

NVL2 (string_to_be_tested, string_if_not_null, string_if_null);

i.e.:

SELECT product.ID, NVL2(product.Name, "Internal", "External") AS ProductName
FROM Product;
Rafael Rossi
  • 1
  • 2