Oracle PL/SQL Tutorial/Conversion Functions/coalesce

COALESCE(state, "N/A")

SQL>
SQL> CREATE TABLE publishers(
  2    pub_id   CHAR(3)     NOT NULL,
  3    pub_name VARCHAR(20) NOT NULL,
  4    city     VARCHAR(15) NOT NULL,
  5    state    CHAR(2)     NULL    ,
  6    country  VARCHAR(15) NOT NULL);
Table created.
SQL>
SQL> INSERT INTO publishers VALUES("P01","A","New York","NY","USA");
1 row created.
SQL> INSERT INTO publishers VALUES("P02","C","San Francisco","CA","USA");
1 row created.
SQL> INSERT INTO publishers VALUES("P03","S","Hamburg",NULL,"Germany");
1 row created.
SQL> INSERT INTO publishers VALUES("P04","T","Berkeley","CA","USA");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT
  2      pub_id,
  3      city,
  4      COALESCE(state, "N/A") AS "state",
  5      country
  6    FROM publishers;
PUB CITY            sta COUNTRY
--- --------------- --- ---------------
P01 New York        NY  USA
P02 San Francisco   CA  USA
P03 Hamburg         N/A Germany
P04 Berkeley        CA  USA
SQL>
SQL> drop table publishers;
Table dropped.
SQL>
SQL>

Use coalesce during the table join

SQL>
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason",  "N",  "TRAINER", 2,   date "1965-12-18",  800 , NULL,  10);
1 row created.
SQL> insert into employees values(2,"Jerry",  "J",  "SALESREP",3,   date "1966-11-19",  1600, 300,   10);
1 row created.
SQL> insert into employees values(3,"Jord",   "T" , "SALESREP",4,   date "1967-10-21",  1700, 500,   20);
1 row created.
SQL> insert into employees values(4,"Mary",   "J",  "MANAGER", 5,   date "1968-09-22",  1800, NULL,  20);
1 row created.
SQL> insert into employees values(5,"Joe",    "P",  "SALESREP",6,   date "1969-08-23",  1900, 1400,  30);
1 row created.
SQL> insert into employees values(6,"Black",  "R",  "MANAGER", 7,   date "1970-07-24",  2000, NULL,  30);
1 row created.
SQL> insert into employees values(7,"Red",    "A",  "MANAGER", 8,   date "1971-06-25",  2100, NULL,  40);
1 row created.
SQL> insert into employees values(8,"White",  "S",  "TRAINER", 9,   date "1972-05-26",  2200, NULL,  40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C",  "DIRECTOR",10,  date "1973-04-27",  2300, NULL,  20);
1 row created.
SQL> insert into employees values(10,"Pink",  "J",  "SALESREP",null,date "1974-03-28",  2400, 0,     30);
1 row created.
SQL>
SQL>
SQL> create table course_schedule
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , trainer    NUMBER(4)
  5  , location   VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL    );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL>
SQL> create table courses
  2  ( code        VARCHAR2(6)
  3  , description VARCHAR2(30)
  4  , category    CHAR(3)
  5  , duration    NUMBER(2)) ;
Table created.
SQL>
SQL>
SQL> insert into courses values("SQL","SQL course",    "GEN",4);
1 row created.
SQL> insert into courses values("OAU","Oracle course", "GEN",1);
1 row created.
SQL> insert into courses values("JAV","Java course",   "BLD",4);
1 row created.
SQL> insert into courses values("PLS","PL/SQL course", "BLD",1);
1 row created.
SQL> insert into courses values("XML","XML course",    "BLD",2);
1 row created.
SQL> insert into courses values("ERM","ERM course",    "DSG",3);
1 row created.
SQL> insert into courses values("PMT","UML course",    "DSG",1);
1 row created.
SQL> insert into courses values("RSD","C# course",     "DSG",2);
1 row created.
SQL> insert into courses values("PRO","C++ course",    "DSG",5);
1 row created.
SQL> insert into courses values("GEN","GWT course",    "DSG",4);
1 row created.
SQL>
SQL>
SQL>
SQL> select DISTINCT c.code
  2  ,      o.begindate
  3  ,      c.duration
  4  ,      case when o.trainer is not null
  5              then e.ename
  6              else null
  7         end       as trainer
  8  from   employees e
  9  ,      courses   c
 10  ,      course_schedule o
 11  where  coalesce(o.trainer,-1) in (e.empno,-1)
 12  and    o.course = c.code;
CODE   BEGINDATE   DURATION TRAINER
------ --------- ---------- --------
JAV    13-DEC-99          4 Joe
OAU    27-SEP-00          1 Yellow
JAV    01-FEB-00          4 Red
RSD    24-FEB-01          2 White
OAU    10-AUG-99          1 Jerry
PRO    19-FEB-01          5
SQL    04-OCT-99          4 Jord
ERM    15-JAN-01          3 Pink
XML    18-SEP-00          2
PLS    11-SEP-00          1 White
SQL    13-DEC-99          4 Mary
SQL    12-APR-99          4 Jason
XML    03-FEB-00          2 Black
13 rows selected.
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
SQL> drop table courses;
Table dropped.
SQL>
SQL>