Menu
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
650
DB2 supports null, and as such, you can use null to distinguish between a deliberate entry of 0 (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry (NULL for both numerical and character columns).
The ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression.
The NOT NULL constraint is used to ensure that a given column of a table is never assigned the null value. Once a NOT NULL constraint has been defined for a particular column, any insert or update operation that attempts to place a null value in that column will fail.
The IS NULL condition is satisfied if the column contains a null value or if the expression cannot be evaluated because it contains one or more null values. If you use the IS NOT NULL operator, the condition is satisfied when the operand is column value that is not null, or an expression that does not evaluate to null.
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.
113
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;
5
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
