Lesson 4	Using the null value BIF with Oracle SQL
Objective	Use the NVL BIF with Oracle SQL.

Using null value BIF with Oracle SQL

Three Valued Logic

The null value BIF of Oracle SQL arises from the three-valued logic that is the foundation of the SQL language.

Null values are missing values

Essentially, three values logic understands that a missing value (what Oracle recognizes as NULL) is NOT the same as the default for that data type. For example, a NULL salary for an employee is not the same thing as a salary of zero.
Hence, you need to be able to display data from Oracle tables, and quickly convert null values.

SELECT 
ename, 
NVL(TO_CHAR(SALARY), 
’NOT YET ASSIGNED’) "SALARY" 
FROM 
employee;
ENAME  SALARY
---------- -----------------
BARRETT    85,000
BURLEEZE  101,000
DAHLSTROM  NOT YET ASSIGNED

Here we are using the NVL clause to supply a value for new employees who have not yet been assigned a salary. Within the NVL clause, we are using the TO_CHAR function to change the numeric salary to a character string labeled “NOT YET ASSIGNED.”

Outer join problems with NULL values

The null value issue is even more problematic when two tables are joined via an outer join. In an outer join, two tables are joined together but there may not always be a corresponding row in both tables. When an outer join is requested, Oracle will display all of the rows of the joining table, even if there is not a matching row in the other table. For these rows, we must handle the NULL values that are returned by the Oracle query.

DBA_DATA_FILES describes database files. Unfortunately, some data files will be 100% full and will not have an entry in DBA_FREE_SPACE.
So what can you do? If there is not an entry in DBA_FREE_SPACE, then how can you indicate that the data file is 100% full?
The following Slide Show shows you how.

Perform outer join using the + operator. An outer join will return rows in dba_data files, even if the values are missing.
As you see from the query, we are querying dba_free_space.bytes, and we know that because there is no entry in dba_free_space for full data files
We have enclosed all queries with the NVL clause
Here is another way to look at the problem. When you join two tables
Because of this, your result will have missing values.
You can set these to zero instead by specifying SELECT NVL(column name, 0).

Null Built In Function
You can view the query that was just observed in the Slideshow in the code below.

SELECT 
B.TABLESPACE_NAME,                                              
SUBSTR(B.FILE_NAME,1,100),                                 
B.FILE_ID,                                                     
NVL(COUNT(*),0) PIECES,
NVL(ROUND
((SUM(b.BYTES)/COUNT(*))/1048576),0) 
ALLOC_MBYTES,
NVL(ROUND
((SUM(A.BYTES))/1048576),0) FREE_MBYTES,
NVL(ROUND
 (100 - 
 (NVL(100-
 (SUM(NVL(A.BYTES,0))/(SUM(NVL(B.BYTES,0))
 /COUNT(*)))*100,0))),0)PCT_FREE_MBYTES,  
NVL(ROUND
 (MAX(A.BYTES/1048576)),0)
CONTIGUOUS_FREE_MBYTES,
NVL(ROUND
 (100 - 
 (NVL(100-
 (MAX(NVL(A.BYTES,0))/(SUM(NVL(B.BYTES,0))
 /COUNT(*)))*100,0))),0)PCT_CONTIGUOUS_FREE,
SYSDATE STATS_DATE,
NVL(ROUND
 ((SUM(B.BYTES)/COUNT(*)-NVL(SUM(A.BYTES),0))
 /1048576),0)USED_MEG,
NVL(ROUND
 (NVL(100-
 (SUM(NVL(A.BYTES,0))/(SUM(NVL(B.BYTES,0))
 /COUNT(*)))*100,0)),0) PCT_USED_MBYTES,   
NULL DB_NAME,
NULL SERVER_NAME
FROM 
SYS.DBA_FREE_SPACE A,                                             
SYS.DBA_DATA_FILES B                                              
WHERE
A.FILE_ID (+) = B.FILE_ID 
AND
B.STATUS = 'AVAILABLE'                                           
GROUP BY 
B.TABLESPACE_NAME, 
B.FILE_NAME, 
B.FILE_ID;

Now, let us look at another powerful translation BIF called DECODE.