Conditional Expressions
Execute logic-based operations based on specified conditions.
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
Last updated