# Conditional Expressions

#### <mark style="color:purple;">CASE</mark>

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

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

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

#### <mark style="color:purple;">COALESCE</mark>

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

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

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

#### <mark style="color:purple;">NULLIF(</mark>  value1,  value2  <mark style="color:purple;">)</mark>

Returns null if `value1` equals `value2`, otherwise returns `value1`

```sql
> select nullif(24, 24);
null

> select nullif(12, null);
12
```

#### <mark style="color:purple;">GREATEST(</mark>  \<expr1>,  \<expr2>,  \<expr3>......\<exprN>  <mark style="color:purple;">)</mark>

Returns the largest value among the given values.

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

#### <mark style="color:purple;">LEAST(</mark>  \<expr1>,  \<expr2>,  \<expr3>......\<exprN>  )

Returns the smallest value among the given values.

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

#### <mark style="color:purple;">IS \[NOT] NULL(</mark>  \<expr>  )

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

```sql
select * from tab where col1 is null;

select * from tab where col1 is not null;
```

#### <mark style="color:purple;">NVL(</mark>  \<expr1>, \<expr2>  )

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

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

#### <mark style="color:purple;">NVL2 (</mark>  \<expr1>, \<expr2>, \<expr3>  )

Returns values depending on whether the first input is NULL. If *expr1* is NOT NULL, then NVL2 returns expr&#x32;*`.`*&#x49;f *expr1* is NULL, then NVL2 returns expr3.

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

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

#### <mark style="color:purple;">DECODE(</mark>  \<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.

<pre class="language-sql"><code class="lang-sql"><strong>> with cte as(
</strong>    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
</code></pre>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.e6data.com/product-documentation/sql-command-reference/conditional-expressions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.