3:12 AM
Deep Dey
I want to know the answer of this question..
100+null=? in sql
This is a question that goes to the heart of SQL. What is NULL? Null in Oracle means a value is undefined or unknown, so the short answer to the question is "unknown" - in other words NULL. In fact the result of any arithmetic operation involving a NULL value is always NULL. This can cause problems because only NULL is ever equal to NULL.
Fortunately Oracle provides us with several tools for dealing with unknown values. There is the reserved word NULL in both SQL and PL/SQL and a number of functions, the most commonly used of which are NVL and DECODE.
The DECODE function enables us to change the value of a column retrieved by a query and is often used to convert numeric codes into something more meaningful. For example, the following SQL statement converts a NULL (unknown) numeric value into the character string 'unknown'.
SELECT DECODE(NULL+10,NULL,'unknown') FROM dual;
The NVL function does the same job but will only convert NULL values whereas the DECODE function can be used for any value. The NVL function also only allows conversion to a value of the same type. This means the following SQL statement generates an "ORA-01722: invalid number" exception in Oracle.
SELECT NVL(NULL+10,'unknown') FROM dual;
This occurs because Oracle casts the NULL+10 as a number and so the NVL is expecting a number as the value to assign to the result of the expression instead of NULL.
We have to provide a numeric value to be assigned to the result instead of NULL.
SELECT NVL(NULL+10,0) FROM dual;
which would produce a result of 0.
By now you might be thinking what happnes with character strings. The answer is nothing. In this case NULL values are regarded as empty strings. This means that the following SQL statement returns 'XX' instead of 'unknown'.
SELECT NVL(NULL||'XX','unknown') FROM dual;
The other SQL functions handling NULLs are
100+null=? in sql
This is a question that goes to the heart of SQL. What is NULL? Null in Oracle means a value is undefined or unknown, so the short answer to the question is "unknown" - in other words NULL. In fact the result of any arithmetic operation involving a NULL value is always NULL. This can cause problems because only NULL is ever equal to NULL.
Fortunately Oracle provides us with several tools for dealing with unknown values. There is the reserved word NULL in both SQL and PL/SQL and a number of functions, the most commonly used of which are NVL and DECODE.
The DECODE function enables us to change the value of a column retrieved by a query and is often used to convert numeric codes into something more meaningful. For example, the following SQL statement converts a NULL (unknown) numeric value into the character string 'unknown'.
SELECT DECODE(NULL+10,NULL,'unknown') FROM dual;
The NVL function does the same job but will only convert NULL values whereas the DECODE function can be used for any value. The NVL function also only allows conversion to a value of the same type. This means the following SQL statement generates an "ORA-01722: invalid number" exception in Oracle.
SELECT NVL(NULL+10,'unknown') FROM dual;
This occurs because Oracle casts the NULL+10 as a number and so the NVL is expecting a number as the value to assign to the result of the expression instead of NULL.
We have to provide a numeric value to be assigned to the result instead of NULL.
SELECT NVL(NULL+10,0) FROM dual;
which would produce a result of 0.
By now you might be thinking what happnes with character strings. The answer is nothing. In this case NULL values are regarded as empty strings. This means that the following SQL statement returns 'XX' instead of 'unknown'.
SELECT NVL(NULL||'XX','unknown') FROM dual;
The other SQL functions handling NULLs are
- COALESCE
- LNNVL
- NANVL
- NULLIF
- NVL
- NVL2
0 comments:
Post a Comment