ifnull

Return the first argument if not null, otherwise return the second argument.

For an overview see page eXtremeSQL Functions

Prototype

     
    ifnull( x, y );
     

Arguments

x	The argument to test for null; returned if x is not null
y	The value to return if x is null

Description

This function is an alternative for coalesce(). It returns x if not null; otherwise it returns y. The arguments 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.

Example 1

The ifnull() function can be used with simple arguments such as the following:

 
    select ifnull( 1, 2), ifnull( null, 2);
    #1      #2
    --------------------------------------------------------
    1       2
     

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 ifnull() to extract the non-null value from each of the specified fields (note that the select results are reformatted here to make it easier to see how they correlate with the fields):

     
    select ifnull(i1, i2) as i, 
        ifnull(ch1, ch2) as ch, 
        ifnull(sc1, sc2) as sc, 
        ifnull(si1, si2) as si, 
         ifnull(a1, a2) as a,
        ifnull(t1, t2) as t,
        ifnull(d1, d2) as d,
        ifnull(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