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>