Replace SQL Null Values with SQLPLUS Set Null Command

You can use the SQL NVL function to handle the null values in the query. Second option is SQLPLUS set null feature to replace the null values with user defined values.

See an example here.
We have a table DEPT with some of the HOD column values are null. 

SQL> select dname, HOD from dept
SQL> /

DNAME          HOD
-------------- ------------------------------
ACCOUNTING     Jones
RESEARCH
SALES
OPERATIONS     Peter 

I am setting the null values to "Not Yet Assigned” as replacement.

SQL> set null "Not Yet Assigned"

SQL>/
DNAME          HOD
-------------- ------------------------------
ACCOUNTING     Jones
RESEARCH       Not Yet Assigned
SALES          Not Yet Assigned
OPERATIONS     Peter
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google