Saturday, March 24, 2012

SQL*Plus SET NULL Statement

I have used the NVL function in several other posts but here is another example. First a table is created. It has never been analyzed so its last_analyzed date is null. The NVL function spots this and replaces the null by the text, Not yet:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> select nvl(to_char(last_analyzed),'Not yet')
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

NVL(TO_CH
---------
Not yet

SQL>

If you don't like the NVL function, you can use the SQL*Plus SET NULL statement instead. Here is one way to use it:

SQL> set null 'Null'
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Null

SQL>

And here is another:

SQL> col last_analyzed null Never
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Never

SQL>

No comments: