Oracle PL/SQL Tutorial/Conversion Functions/coalesce
COALESCE(state, "N/A")
<source lang="sql">
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></source>
Use coalesce during the table join
<source lang="sql">
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></source>
