SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
9-29
Examples of COALESCE
values('John', 60, 70, 80, 60);
--- 1 row(s) inserted.
-- This inserts the English marks for Harry, in addition to
math, science and economics.
insert into student(name, math, science, economics, english)
values('Harry', 50, 80, 75, 55);
--- 1 row(s) inserted.
-- This inserts the German marks for Mike, in addition to
math, science and economics.
insert into student(name, math, science, economics, german)
values('Mike', 90, 50, 60, 80);
--- 1 row(s) inserted.
-- This inserts the Spanish marks for Raul, in addition to
math, science and economics.
insert into student(name, math, science, economics, spanish)
values('Raul', 60, 70, 80, 70);
--- 1 row(s) inserted.
-- This inserts the Spanish marks for Leo, in addition to
math, science and economics.
insert into student(name, math, science, economics, spanish)
values('Leo', 60, 70, 80, 35);
--- 1 row(s) inserted.
-- This inserts the English marks for Donald, in addition to
math, science and economics.
insert into student(name, math, science, economics, english)
values('Donald',60, 70, 80, 25);
--- 1 row(s) inserted.
-- The COALESCE function selects the language marks opted by
each student.
select name, math, science, economics, coalesce(english,
french, spanish, german) as language_mark from student;
NAME MATH SCIENCE ECONOMICS LANGUAGE_MARK
-------------------- ----------- ----------- -----------
John 60 70 80 60
Harry 50 80 75 55
Mike 90 50 60 80
Raul 60 70 80 70
Leo 60 70 80 35
Donald 60 70 80 25
--- 6 row(s) selected.