RelationalDBDesign 





ANSI SQL Extensions  «Prev  Next»
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

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.

An example

For example, view the code below to see a query that computes data file usage by querying the DBA_DATA_FILES view and the DBA_FREE_SPACE view.

TABLESPACE_NAME
------------------------------
SUBSTR(B.FILE_NAME,1,100)
--------------------------------------------------------------------------------
   FILE_ID     PIECES ALLOC_MBYTES FREE_MBYTES PCT_FREE_MBYTES
---------- ---------- ------------ ----------- ---------------
CONTIGUOUS_FREE_MBYTES PCT_CONTIGUOUS_FREE STATS_DAT   USED_MEG PCT_USED_MBYTES
---------------------- ------------------- --------- ---------- ---------------
D S
- -
SYSTEM
/ora7/home/dba/oracle/admin/don1/data/dfs1/don1_sys1.dbf
         1        142           50          14              28
                    11                  22 19-OCT-2009         36              72


RBS
/ora7/home/dba/oracle/admin/don1/data/dfs3/don1_rbs1.dbf
         2         11          675         655              97
                   585                  87 19-OCT-2009         20               3


TEMP
/ora7/home/dba/oracle/admin/don1/data/dfs1/don1_tmp1.dbf
         3         32          500         500             100
                    16                   3 19-OCT-2009          0               0                                 

This is a very common DBA script and it is used to show the amount of data file space that is available to Oracle.
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.



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.