Conditional Expressions

CASE

Returns the result of the first condition evaluating to true or returns the default value if all conditions evaluate to false.

SYNTAX - CASE
             WHEN condition1 THEN result1
             [WHEN condition2 THEN result2 ]
             [WHEN ......]
             [ELSE default ]
         END

> select col1,
               case
                    when col1 < 0 then 'res1'
                    when col1 > 0 then 'res2'
                    else 'res3'
               end as result
           from table

COALESCE

Returns the first non-null value from a list of expressions.

coalesce(val1, val2, val3 ......)

> select coalesce(NULL,20,NULL,NULL,'String1');
20

NULLIF( value1, value2 )

Returns null if value1 equals value2, otherwise returns value1

> select nullif(24, 24);
null

> select nullif(12, null);
12

GREATEST( <expr1>, <expr2>, <expr3>......<exprN> )

Returns the largest value among the given values.

select greatest(100, 12, 23, 1999, 2)
1999

LEAST( <expr1>, <expr2>, <expr3>......<exprN> )

Returns the smallest value among the given values.

select least(-12, 0, 10000, -527)
-527 

IS [NOT] NULL( <expr> )

Returns boolean based on the expr is null or is not null

select * from tab where col1 is null;

select * from tab where col1 is not null;

NVL( <expr1>, <expr2> )

If expr1 is NULL, returns expr2, otherwise returns expr1.

> select nvl(null, 'e6data');
e6data

NVL2 ( <expr1>, <expr2>, <expr3> )

Returns values depending on whether the first input is NULL. If expr1 is NOT NULL, then NVL2 returns expr2.If expr1 is NULL, then NVL2 returns expr3.

> select nvl2('e6', 'e6data', 'data');
e6data

> select nvl2('e6', 'e6data', 'data');
data

DECODE( <expr> , <search_1> , <result_1> [ , <search_2> , <result_2> ... ] [ , <default>] )

Compares each search expression sequentially against the select expression. Upon finding a match, it returns the corresponding result expression.

> with cte as(
    select 1 as c1
    union all
    select 2
    union all
    select (NULL)
    union all
    select 4
)
select c1,decode(c1,1,'one',2,'two',null,'-xyx','others')
from cte
order by 1,2
--
|1    |one
|2    |two
|4    |others
|null    | -xyx