SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-28
Examples of COALESCE
•
The COALESCE function has no limit on the number of arguments, other than the
general limit of an SQL expression. However, large lists of expressions do not
perform well.
Examples of COALESCE
•
This function returns the first NOT NULL value from the expression list (msg, warn,
err). For the first row, value for column warn is returned. For the second row, value
for column err is returned.
create table tab1(msg varchar(20), warn varchar(20), err
varchar(20));
-- The Default for column msg, column warn and column err is
NULL
insert into tab1(warn) values('xxxx');
insert into tab1(err) values('zzzz');
-- In the first row
select COALESCE(msg,warn,err), msg, warn, err from tab1;
>>select COALESCE(msg,warn,err), msg, warn, err from tab1;
(EXPR) MSG WARN ERR
---- ------- ------- -----
xxxx ? xxxx ?
zzzz ? ? zzzz
•
The function returns the language marks for the students. Assume that each
student can only choose one language course (English, French, Spanish, or
German).
Create table student( name varchar(20),
math int,
science int,
economics int,
english int,
french int,
spanish int,
german int);
--- SQL operation complete.
-- This inserts the French marks for John, in addition to
math, science and economics.
insert into student(name, math, science, economics, french)
Note. Existing NonStop SQL/MX conversions rules are applied to the above
rules.