nullifzero

Return null if the first argument is zero, otherwise return the first argument.

For an overview see page eXtremeSQL Functions

Prototype

     
    nullifzero( x );
     

Arguments

x	The argument to test for null; returned if x is not zero

Description

This function returns null if x=0; x if not. The argument can be a constant value or a table column of any non-character scalar type.

Example 1

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

 
    select nullifzero(1), nullifzero(0);
    #1      #2
    --------------------------------------------------------
    1       null
     
    select ifnull( 1, nullifzero(2)), ifnull( nullifzero(0), 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,
        t1 time, t2 time,
        d1 decimal(15,2), d2 decimal(15,2),
        b1 boolean, b2 boolean
    );
    insert into t values(0, 1, 
                0, '04-02-2019 10:19', 
                0, 10.2,
                0, true);
    insert into t values(2, 0, 
                '04-02-2019 10:23', 0,
                10.02, 0,
                0, false);
                 

The following select statement uses function nullifzero() to return null where column values are zero:

     
    select nullifzero(i1) i1, nullifzero(i2) i2,
        nullifzero(t1) t1, nullifzero(t2) t2,
        nullifzero(d1) d1, nullifzero(d2) d2,
        nullifzero(b1) b1, nullifzero(b2) b2
         from t;
 
    i1      i2      t1      t2      d1      d2      b1      b2
    --------------------------------------------------------
    null    1       null    04/02/2019 10:19:00     null    10.20   null    true
    2       null    04/02/2019 10:23:00     null    10.02   null    null    null