The NULLIF function returns a NULL value if both parameters are equal in value. If the parameters are not equal, it returns the value of the first parameter. The following query would return NULL.
SELECT NULLIF(1,1) FROM dual;
The COALESCE function returns the first non-NULL value in an expression list. If all expressions are null it returns NULL. The following query would return '5'.
SELECT COALESCE(NULL, NULL, '5') FROM dual;
The Oracle/PLSQL NVL function Converts NULL value to actual value.The following query will return 5.
Syntax: NVL( value, replace_if_value_is_null ) SELECT NVL(NULL,5) FROM dual;
The Oracle/PLSQL NVL2 function extends the functionality found in the NVL functions. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.
If expr1 is not NULL, NVL2 returns expr2. If expr1 is NULL, NVL2 returns expr3. The argument expr1 can have any data type.
Syntax: NVL2( expr1, value_if_expr1_not_null, value_if_expr1_null ) SELECT NVL2(NULL,5,7) FROM dual; Output : 7 SELECT NVL2('dEexams',5,7) FROM dual; Output : 5
The Oracle/PLSQL LNNVL function is used in the WHERE clause of a SQL statement to evaluate a condition when one of the operands may contain a NULL value.
Syntax: LNNVL( condition )
Condition Evaluates To | LNNVL Return Value |
---|---|
TRUE | FALSE |
FALSE | TRUE |
UNKNOWN | TRUE |
Example:
Consider the product table with below data.
prod_id | qty | sale |
---|---|---|
1 | 100 | NULL |
2 | 200 | 300 |
3 | 300 | 200 |
4 | 400 | 100 |
Now if we wanted to find all the product whose qty was below their sale.The SQL will be as follows .
SELECT * FROM product WHERE qty < sale; Output : prod_id qty sale 2 200 300
However, if we wanted to see the products that were below their sale as well as NULL sales, we would use the LNNVL functions as follows:
SELECT * FROM product WHERE LNNVL(qty >= sale) ; Output : prod_id qty sale 1 100 NULL 2 200 300
The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0.
Syntax: NANVL( value, replace_with ) SELECT NANVL(binary1,0) FROM test; Output : The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value. SELECT NANVL(binary1,NULL) FROM test; Output : The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.