# coalesce

> Return the first non-null value in a list of arguments.

> ### For an overview see page eXtremeSQL Functions

> ### Prototype

>         coalesce( arg1, arg2, ... argN);

> ### Arguments

> `arg1, arg2, ... argN` | A list each element of which can be a constant value, an array, a sequence, time or a table column of any of these types

> ### Description

> > This function returns the first non-null value in a list of arguments. The list is composed of elements each of which can be a constant value, an array, a `sequence`, `time`, or a table column of any of these types. This can be useful when a result is required but it is not know when a field value is present.

> > For example, consider a table Person with three types of contact phone number: `Business_Phone`, `Cell_Phone` and `Home_Phone`.

> > If we want to get a contact number for each person we might specify the following select statement to extract the first non-null value:

> >         select Name, coalesce( Business_Phone, Cell_Phone, Home_Phone) as Contact_Phone
> >         from Person;

> > ( See the example 3 below.)

> ### Example 1

> The `coalesce()` function can be used with simple lists such as the following:

>         select coalesce( 1, 2, 3), coalesce( null, null, 3);
>         #1      #2
>         --------------------------------------------------------
>         1       3

> ### Example 2

> Or it can be used with table columns of different types. For example, consider the table `t` below initialized with the following two rows of values:

>         create table t(
>             i1 int, i2 int,
>             ch1 char(20), ch2 char(20),
>             sc1 sequence(char(5)), sc2 sequence(char(5)),
>             si1 sequence(int), si2 sequence(int),
>             a1 array(int), a2 array(int),
>             t1 time, t2 time,
>             d1 decimal(15,2), d2 decimal(15,2),
>             b1 boolean, b2 boolean
>         );
>         insert into t values(null, 1,
>                     null, 'ch1',
>                     null, ['1','2','3','4','5'],
>                     null, [1,2,3,4,5],
>                     null, [1,2,3,4,5,6,7,8,9,0],
>                     null, '04-02-2019 10:19',
>                     null, 10.2,
>                     null, true);
>         insert into t values(2, null,
>                     'ch2', null,
>                     ['a1','a2','a3','a4','a5'], null,
>                     [11,12,13,14,15], null,
>                     [10,20,30,40,50], null,
>                     '04-02-2019 10:23', null,
>                     10.02, null,
>                     null, false);

> The following `select` statement uses function `coalesce()` to extract the first non-null value from each of the specified lists of fields (note that the `select` results are reformatted here to make it easier to see how they correlate with the fields):

>         select coalesce(i1, i2) as i,
>             coalesce(ch1, ch2) as ch,
>             coalesce(sc1, sc2) as sc,
>             coalesce(si1, si2) as si,
>              coalesce(a1, a2) as a,
>             coalesce(t1, t2) as t,
>             coalesce(d1, d2) as d,
>             coalesce(b1, b2) as b
>              from t order by i;

>             i
>               ch
>         sc
>         si
>              a
>               t
>               d
>               b
>         --------------------------------------------------------
>         1
>               ch1
>             {1, 2, 3, 4, 5}
>         {1, 2, 3, 4, 5}
>         [1, 2, 3, 4, 5, 6, 7, 8, 9, 0]
>         04/02/2019 10:19:00
>             10.20
>         true

>         2
>         ch2
>             {a1, a2, a3, a4, a5}
>            {11, 12, 13, 14, 15}
>            [10, 20, 30, 40, 50]
>         04/02/2019 10:23:00
>         10.02
>           false

> ### Example 3

> The practical example outlined in the description above could use a table Person defined and initialized as follows:

>         create table Person( Name char(20), Business_Phone char(20),
>                     Cell_Phone char(20), Home_Phone char(20));
>         insert into Person values( 'Hal', '888-555-9999', '888-555-1111', '888-555-3333');
>         insert into Person values( 'Barb', null, '888-555-2222', '888-555-4444');
>         insert into Person values( 'Doug', null, null, '888-555-5555');

>         select * from Person;
>         Name    Business_Phone  Cell_Phone      Home_Phone
>         --------------------------------------------------------
>         Hal     888-555-9999    888-555-1111    888-555-3333
>         Barb    null    888-555-2222    888-555-4444
>         Doug    null    null    888-555-5555

> Now we could use the following `select` to extract a contact number for each table row with the following rules:

> > 1. If a person has a business phone, use the business phone number.

> > 2. If a person does not have a business phone and has a cell phone, use the cell phone number.

> > 3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

>         select Name, coalesce( Business_Phone, Cell_Phone, Home_Phone) as Contact_Phone
>         from Person;

>         Name    Contact_Phone
>         --------------------------------------------------------
>         Hal     888-555-9999
>         Barb    888-555-2222
>         Doug    888-555-5555