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.
hope this might help someone else out there
SELECT
.... FROM XXX XX
WHERE
....
AND(
param1 IS NULL
OR XX.param1 = param1
)
COALESCE
function same ISNULL
function
Note. you must use COALESCE
function with same data type of column that you check is null.
I'm not familiar with DB2, but have you tried COALESCE?
ie:
SELECT Product.ID, COALESCE(product.Name, "Internal") AS ProductName
FROM Product
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.
hope this might help someone else out there
SELECT
.... FROM XXX XX
WHERE
....
AND(
param1 IS NULL
OR XX.param1 = param1
)
I'm not familiar with DB2, but have you tried COALESCE?
ie:
SELECT Product.ID, COALESCE(product.Name, "Internal") AS ProductName
FROM Product
Select Product.ID, VALUE(product.Name, "Internal") AS ProductName from Product
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.
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;
COALESCE
function same ISNULL
function
Note. you must use COALESCE
function with same data type of column that you check is null.
Select Product.ID, VALUE(product.Name, "Internal") AS ProductName from Product
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;
I'm not familiar with DB2, but have you tried COALESCE?
ie:
SELECT Product.ID, COALESCE(product.Name, "Internal") AS ProductName
FROM Product
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.
I'm not familiar with DB2, but have you tried COALESCE?
ie:
SELECT Product.ID, COALESCE(product.Name, "Internal") AS ProductName
FROM Product
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.
Source: Stackoverflow.com