This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Conditional Functions

1: [ NOT ] BETWEEN
2: [ NOT ] IN
3: AND
4: CASE
5: COALESCE
6: Comparison Methods
7: ERROR_OR
8: GREATEST
9: IF
10: IFNULL
11: IS [ NOT ] DISTINCT FROM
12: IS_ERROR
13: IS_NOT_ERROR
14: IS_NOT_NULL
15: IS_NULL
16: LEAST
17: NULLIF
18: NVL
19: NVL2
20: OR

This section provides reference information for the conditional functions in PlaidCloud Lakehouse.

1 - [ NOT ] BETWEEN

Returns true if the given numeric or string <expr> falls inside the defined lower and upper limits.

Analyze Syntax

table.column.between(<lower_limit>, <upper_limit>

Analyze Examples

table.column.between(0, 5)

SQL Syntax

<expr> [ NOT ] BETWEEN <lower_limit> AND <upper_limit>

SQL Examples

SELECT 'true' WHERE 5 BETWEEN 0 AND 5;

┌────────┐
│ 'true' │
├────────┤
│ true   │
└────────┘

SELECT 'true' WHERE 'data' BETWEEN 'data' AND 'databendcloud';

┌────────┐
│ 'true' │
├────────┤
│ true   │
└────────┘

2 - [ NOT ] IN

Checks whether a value is (or is not) in an explicit list.

Analyze Syntax

table.columns.in_((<value1>, <value2> ...))

Analyze Examples

table.columns.in_((<value1>, <value2> ...))

┌──────────────────────────┐
│ table.column.in_((2, 3)) │
├──────────────────────────┤
│ true                     │
└──────────────────────────┘

SQL Syntax

<value> [ NOT ] IN (<value1>, <value2> ...)

SQL Examples

SELECT 1 NOT IN (2, 3);

┌────────────────┐
│ 1 not in(2, 3) │
├────────────────┤
│ true           │
└────────────────┘

3 - AND

Conditional AND operator. Checks whether both conditions are true.

Analyze Syntax

and_(<expr1>[, <expr2> ...])

Analyze Examples

and_(  
    table.color == 'green',  
    table.shape == 'circle',  
    table.price >= 1.25  
)

SQL Syntax

<expr1> AND <expr2>

SQL Examples

SELECT * FROM table WHERE
    table.color = 'green'
    AND table.shape = 'circle'  
    AND table.price >= 1.25;

4 - CASE

Handles IF/THEN logic. It is structured with at least one pair of WHEN and THEN statements. Every CASE statement must be concluded with the END keyword. The ELSE statement is optional, providing a way to capture values not explicitly specified in the WHEN and THEN statements.

SQL Syntax

case(
    (<condition_1>, <value_1>),
    (<condition_2>, <value_2>),
    [ ... ]
    [ else_=<value_n>]
)

Analyze Examples

A simple example

This example returns a person's name. It starts off searching to see if the first name column has a value (the "if"). If there is a value, concatenate the first name with the last name and return it (the "then"). If there isn't a first name, then return the last name only (the "else").

case(
    (table.first_name.is_not(None), func.concat(table.first_name, table.last_name)), 
    else_=table.last_name
)

A more complex example with multiple conditions

This example returns a price based on quantity. "If" the quantity in the order is more than 100, then give the customer the special price. If it doesn't satisfy the first condition, go to the second. If the quantity is greater than 10 (11-100), then give the customer the bulk price. Otherwise give the customer the regular price.

case( 
    (order_table.qty > 100, item_table.specialprice), 
    (order_table.qty > 10, item_table.bulkprice), 
    else_=item_table.regularprice
)

This example returns the first initial of the person's first name. If the user's name is wendy, return W. Otherwise if the user's name is jack, return J. Otherwise return E.

case( 
    (users_table.name == "wendy", "W"), 
    (users_table.name == "jack", "J"), 
    else_='E'
)

The above may also be written in shorthand as:

case(
    {"wendy": "W", "jack": "J"}, 
    value=users_table.name, 
    else_='E' 
)

Other Examples

In this example is from a Table:Lookup step where we are updating the "dock_final" column when the table1.dock_final value is Null.

case(
    (table1.dock_final == Null, table2.dock_final),
    else_ = table1.dock_final
)

This example is from a Table:Lookup step where we are updating the "Marketing Channel" column when "Marketing Channel" in table1 is not 'none' or the "Serial Number" contains a '_'.

case(
    (get_column(table1, 'Marketing Channel') != 'none', get_column(table1, 'Marketing Channel')),
    (get_column(table1, 'Serial Number').contains('_'), get_column(table1, 'Marketing Channel')),
    (get_column(table2, 'Marketing Channel').is_not(Null), get_column(table2, 'Marketing Channel')), 
    else_ = 'none'
)

SQL Syntax

CASE
    WHEN <condition_1> THEN <value_1>
  [ WHEN <condition_2> THEN <value_2> ]
  [ ... ]
  [ ELSE <value_n> ]
END AS <column_name>

SQL Examples

This example categorizes employee salaries using a CASE statement, presenting details with a dynamically assigned column named "SalaryCategory":

-- Create a sample table
CREATE TABLE Employee (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT
);

-- Insert some sample data
INSERT INTO Employee VALUES (1, 'John', 'Doe', 50000);
INSERT INTO Employee VALUES (2, 'Jane', 'Smith', 60000);
INSERT INTO Employee VALUES (3, 'Bob', 'Johnson', 75000);
INSERT INTO Employee VALUES (4, 'Alice', 'Williams', 90000);

-- Add a new column 'SalaryCategory' using CASE statement
-- Categorize employees based on their salary
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    CASE
        WHEN Salary < 60000 THEN 'Low'
        WHEN Salary >= 60000 AND Salary < 80000 THEN 'Medium'
        WHEN Salary >= 80000 THEN 'High'
        ELSE 'Unknown'
    END AS SalaryCategory
FROM
    Employee;

┌──────────────────────────────────────────────────────────────────────────────────────────┐
│    employeeid   │     firstname    │     lastname     │      salary     │ salarycategory │
├─────────────────┼──────────────────┼──────────────────┼─────────────────┼────────────────┤
│               1 │ John             │ Doe              │           50000 │ Low            │
│               2 │ Jane             │ Smith            │           60000 │ Medium         │
│               4 │ Alice            │ Williams         │           90000 │ High           │
│               3 │ Bob              │ Johnson          │           75000 │ Medium         │
└──────────────────────────────────────────────────────────────────────────────────────────┘

5 - COALESCE

Returns the first non-NULL expression within its arguments; if all arguments are NULL, it returns NULL.

Analyze Syntax

func.coalesce(<expr1>[, <expr2> ...])

Analyze Examples

func.coalesce(table.UOM,  'none', \n)

func.coalesce(get_column(table2, 'TECHNOLOGY_RATE'), 0.0)

func.coalesce(table_beta.adjusted_price, table_alpha.override_price, table_alpha.price) * table_beta.quantity_sold

SQL Syntax

COALESCE(<expr1>[, <expr2> ...])

SQL Examples

SELECT COALESCE(1), COALESCE(1, NULL), COALESCE(NULL, 1, 2);

┌────────────────────────────────────────────────────────┐
│ coalesce(1) │ coalesce(1, null) │ coalesce(null, 1, 2) │
├─────────────┼───────────────────┼──────────────────────┤
│           1 │                 1 │                    1 │
└────────────────────────────────────────────────────────┘

SELECT COALESCE('a'), COALESCE('a', NULL), COALESCE(NULL, 'a', 'b');

┌────────────────────────────────────────────────────────────────┐
│ coalesce('a') │ coalesce('a', null) │ coalesce(null, 'a', 'b') │
├───────────────┼─────────────────────┼──────────────────────────┤
│ a             │ a                   │ a                        │
└────────────────────────────────────────────────────────────────┘

6 - Comparison Methods

These comparison methods are available in Analyze expressions.

Category	Expression	Structure	Example	Description
General Usage	>	>	table.column > 23	Greater Than
General Usage	<	<	table.column < 23	Less Than
General Usage	>=	>=	table.column >= 23	Greater than or equal to
General Usage	<=	<=	table.column <= 23	Less than or equal to
General Usage	==	==	table.column == 23	Equal to
General Usage	!=	!=	table.column != 23	Not Equal to
General Usage	and_	and_()	and_(table.a > 23, table.b == u'blue') Additional Examples	Creates an AND SQL condition
General Usage	any_	any_()	table.column.any(('red', 'blue', 'yellow'))	Applies the SQL ANY() condition to a column
General Usage	between	between	table.column.between(23, 46) get_column(table, 'LAST_CHANGED_DATE').between({start_date}, {end_date})	Applies the SQL BETWEEN condition
General Usage	contains	contains	table.column.contains('mno') table.SOURCE_SYSTEM.contains('TEST')	Applies the SQL LIKE '%%'
General Usage	endswith	endswith	table.column.endswith('xyz') table.Parent.endswith(':EBITX') table.PERIOD.endswith("01")	Applies the SQL LIKE '%%'
General Usage	FALSE	FALSE	FALSE	False, false, FALSE - Alias for Python False
General Usage	ilike	ilike	table.column.ilike('%foobar%')	Applies the SQL ILIKE method
General Usage	in_	in_()	table.column.in_((1, 2, 3)) get_column(table, 'Source Country').in_(['CN','SG','BR']) table.MONTH.in_(['01','02','03','04','05','06','07','08','09'])	Test if values are with a tuple of values
General Usage	is_	is_	table.column.is_(None) get_column(table, 'Min SafetyStock').is_(None) get_column(table, 'date_pod').is_(None)	Applies the SQL is the IS for things like IS NULL
General Usage	isnot	isnot	table.column.isnot(None)	Applies the SQL is the IS for things like IS NOT NULL
General Usage	like	like	table.column.like('%foobar%') table.SOURCE_SYSTEM.like('%Adjustments%')	Applies the SQL LIKE method
General Usage	not_	not_()	not_(and_(table.a > 23, table.b == u'blue'))	Inverts the condition
General Usage	notilike	notilike	table.column.notilike('%foobar%')	Applies the SQL NOT ILIKE method
General Usage	notin	notin	table.column.notin((1, 2, 3)) table.LE.notin_(['12345','67890'])	Inverts the IN condition
General Usage	notlike	notlike	table.column.notlike('%foobar%')	Applies the SQL NOT LIKE method
General Usage	NULL	NULL	NULL	Null, null, NULL - Alias for Python None
General Usage	or_	or_()	or_(table.a > 23, table.b == u'blue') Additional Examples	Creates an OR SQL condition
General Usage	startswith	startswith	table.column.startswith('abc') get_column(table, 'Zip Code').startswith('9') get_column(table1, 'GL Account').startswith('CORP')	Applies the SQL LIKE '%'
General Usage	TRUE	TRUE	TRUE	True, true, TRUE - Alias for Python True
Math Expressions	+	+	+	2+3=5
Math Expressions	–	–	-	2–3=-1
Math Expressions	*	*	*	2*3=6
Math Expressions	/	/	/	4/2=2
Math Expressions	column.op	column.op(operator)	column.op('%')	5%4=1
Math Expressions	column.op	column.op(operator)	column.op('^')	2.0^3.0=8
Math Expressions	column.op	column.op(operator)	column.op('!')	5!=120
Math Expressions	column.op	column.op(operator)	column.op('!!')	!!5=120
Math Expressions	column.op	column.op(operator)	column.op('@')	@-5.0=5
Math Expressions	column.op	column.op(operator)	column.op('&')	91&15=11
Math Expressions	column.op	column.op(operator)	column.op('#')	17##5=20
Math Expressions	column.op	column.op(operator)	column.op('~')	~1=-2
Math Expressions	column.op	column.op(operator)	column.op('<<')	1<<4=16
Math Expressions	column.op	column.op(operator)	column.op('>>')	8>>2=2

7 - ERROR_OR

Returns the first non-error expression among its inputs. If all expressions result in errors, it returns NULL.

Analyze Syntax

func.error_or(expr1, expr2, ...)

Analyze Examples

# Returns the valid date if no errors occur
# Returns the current date if the conversion results in an error
func.now(), func.error_or(func.to_date('2024-12-25'), func.now())

┌──────────────────────────────────────────────────────────────────────────────────────────┐
│            func.now()           │ func.error_or(func.to_date('2024-12-25'), func.now())  │
├─────────────────────────────────┼────────────────────────────────────────────────────────┤
│ 2024-03-18 01:22:39.460320      │ 2024-12-25                                             │
└──────────────────────────────────────────────────────────────────────────────────────────┘

# Returns NULL because the conversion results in an error
func.error_or(func.to_date('2024-1234'))

┌────────────────────────────────────────────┐
│ func.error_or(func.to_date('2024-1234'))   │
├────────────────────────────────────────────┤
│ NULL                                       │
└────────────────────────────────────────────┘

SQL Syntax

ERROR_OR(expr1, expr2, ...)

SQL Examples

-- Returns the valid date if no errors occur
-- Returns the current date if the conversion results in an error
SELECT NOW(), ERROR_OR('2024-12-25'::DATE, NOW()::DATE);

┌────────────────────────────────────────────────────────────────────────┐
│            now()           │ error_or('2024-12-25'::date, now()::date) │
├────────────────────────────┼───────────────────────────────────────────┤
│ 2024-03-18 01:22:39.460320 │ 2024-12-25                                │
└────────────────────────────────────────────────────────────────────────┘

-- Returns NULL because the conversion results in an error
SELECT ERROR_OR('2024-1234'::DATE);

┌─────────────────────────────┐
│ error_or('2024-1234'::date) │
├─────────────────────────────┤
│ NULL                        │
└─────────────────────────────┘

8 - GREATEST

Returns the maximum value from a set of values.

Analyze Syntax

func.greatest(<value1>, <value2> ...)

Analyze Examples

func.greatest((5, 9, 4))

┌──────────────────────────┐
│ func.greatest((5, 9, 4)) │
├──────────────────────────┤
│                        9 │
└──────────────────────────┘

SQL Syntax

GREATEST(<value1>, <value2> ...)

SQL Examples

SELECT GREATEST(5, 9, 4);

┌───────────────────┐
│ greatest(5, 9, 4) │
├───────────────────┤
│                 9 │
└───────────────────┘

9 - IF

If <cond1> is TRUE, it returns <expr1>. Otherwise if <cond2> is TRUE, it returns <expr2>, and so on.

Analyze Syntax

func.if(<cond1>, <expr1>, [<cond2>, <expr2> ...], <expr_else>)

Analyze Examples

func.if((1 > 2), 3, (4 < 5), 6, 7)

┌────────────────────────────────────┐
│ func.if((1 > 2), 3, (4 < 5), 6, 7) │
├────────────────────────────────────┤
│                                  6 │
└────────────────────────────────────┘

SQL Syntax

IF(<cond1>, <expr1>, [<cond2>, <expr2> ...], <expr_else>)

SQL Examples

SELECT IF(1 > 2, 3, 4 < 5, 6, 7);

┌───────────────────────────────┐
│ if((1 > 2), 3, (4 < 5), 6, 7) │
├───────────────────────────────┤
│                             6 │
└───────────────────────────────┘

10 - IFNULL

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

Analyze Syntax

func.ifnull(<expr1>, <expr2>)

Analyze Examples

func.ifnull(null, 'b'), func.ifnull('a', 'b')

┌────────────────────────────────────────────────┐
│ func.ifnull(null, 'b') │ func.ifnull('a', 'b') │
├────────────────────────┼───────────────────────┤
│ b                      │ a                     │
└────────────────────────────────────────────────┘

func.ifnull(null, 2), func.ifnull(1, 2)

┌──────────────────────────────────────────┐
│ func.ifnull(null, 2) │ func.ifnull(1, 2) │
├──────────────────────┼───────────────────┤
│                    2 │                 1 │
└──────────────────────────────────────────┘

SQL Syntax

IFNULL(<expr1>, <expr2>)

Aliases

SQL Examples

SELECT IFNULL(NULL, 'b'), IFNULL('a', 'b');

┌──────────────────────────────────────┐
│ ifnull(null, 'b') │ ifnull('a', 'b') │
├───────────────────┼──────────────────┤
│ b                 │ a                │
└──────────────────────────────────────┘

SELECT IFNULL(NULL, 2), IFNULL(1, 2);

┌────────────────────────────────┐
│ ifnull(null, 2) │ ifnull(1, 2) │
├─────────────────┼──────────────┤
│               2 │            1 │
└────────────────────────────────┘

11 - IS [ NOT ] DISTINCT FROM

Compares whether two expressions are equal (or not equal) with awareness of nullability, meaning it treats NULLs as known values for comparing equality.

SQL Syntax

<expr1> IS [ NOT ] DISTINCT FROM <expr2>

SQL Examples

SELECT NULL IS DISTINCT FROM NULL;

┌────────────────────────────┐
│ null is distinct from null │
├────────────────────────────┤
│ false                      │
└────────────────────────────┘

12 - IS_ERROR

Returns a Boolean value indicating whether an expression is an error value.

Analyze Syntax

func.is_error( <expr> )

Analyze Examples

# Indicates division by zero, hence an error
func.is_error((1 / 0)), func.is_not_error((1 / 0))

┌─────────────────────────────────────────────────────┐
│ func.is_error((1 / 0)) │ func.is_not_error((1 / 0)) │
├────────────────────────┼────────────────────────────┤
│ true                   │ false                      │
└─────────────────────────────────────────────────────┘

# The conversion to DATE is successful, hence not an error
func.is_error(func.to_date('2024-03-17')), func.is_not_error(func.to_date('2024-03-17'))

┌───────────────────────────────────────────────────────────────────────────────────────────┐
│ func.is_error(func.to_date('2024-03-17')) │ func.is_not_error(func.to_date('2024-03-17')) │
├───────────────────────────────────────────┼───────────────────────────────────────────────┤
│ false                                     │ true                                          │
└───────────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

IS_ERROR( <expr> )

Return Type

Returns true if the expression is an error, otherwise false.

SQL Examples

-- Indicates division by zero, hence an error
SELECT IS_ERROR(1/0), IS_NOT_ERROR(1/0);

┌───────────────────────────────────────────┐
│ is_error((1 / 0)) │ is_not_error((1 / 0)) │
├───────────────────┼───────────────────────┤
│ true              │ false                 │
└───────────────────────────────────────────┘

-- The conversion to DATE is successful, hence not an error
SELECT IS_ERROR('2024-03-17'::DATE), IS_NOT_ERROR('2024-03-17'::DATE);

┌─────────────────────────────────────────────────────────────────┐
│ is_error('2024-03-17'::date) │ is_not_error('2024-03-17'::date) │
├──────────────────────────────┼──────────────────────────────────┤
│ false                        │ true                             │
└─────────────────────────────────────────────────────────────────┘

13 - IS_NOT_ERROR

Returns a Boolean value indicating whether an expression is an error value.

Analyze Syntax

func.is_error( <expr> )

Analyze Examples

# Indicates division by zero, hence an error
func.is_error((1 / 0)), func.is_not_error((1 / 0))

┌─────────────────────────────────────────────────────┐
│ func.is_error((1 / 0)) │ func.is_not_error((1 / 0)) │
├────────────────────────┼────────────────────────────┤
│ true                   │ false                      │
└─────────────────────────────────────────────────────┘

# The conversion to DATE is successful, hence not an error
func.is_error(func.to_date('2024-03-17')), func.is_not_error(func.to_date('2024-03-17'))

┌───────────────────────────────────────────────────────────────────────────────────────────┐
│ func.is_error(func.to_date('2024-03-17')) │ func.is_not_error(func.to_date('2024-03-17')) │
├───────────────────────────────────────────┼───────────────────────────────────────────────┤
│ false                                     │ true                                          │
└───────────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

IS_NOT_ERROR( <expr> )

Return Type

Returns true if the expression is not an error, otherwise false.

SQL Examples

-- Indicates division by zero, hence an error
SELECT IS_ERROR(1/0), IS_NOT_ERROR(1/0);

┌───────────────────────────────────────────┐
│ is_error((1 / 0)) │ is_not_error((1 / 0)) │
├───────────────────┼───────────────────────┤
│ true              │ false                 │
└───────────────────────────────────────────┘

-- The conversion to DATE is successful, hence not an error
SELECT IS_ERROR('2024-03-17'::DATE), IS_NOT_ERROR('2024-03-17'::DATE);

┌─────────────────────────────────────────────────────────────────┐
│ is_error('2024-03-17'::date) │ is_not_error('2024-03-17'::date) │
├──────────────────────────────┼──────────────────────────────────┤
│ false                        │ true                             │
└─────────────────────────────────────────────────────────────────┘

14 - IS_NOT_NULL

Checks whether a value is not NULL.

Analyze Syntax

func.is_not_null(<expr>)

Analyze Examples

func.is_not_null(1)

┌─────────────────────┐
│ func.is_not_null(1) │
├─────────────────────┤
│ true                │
└─────────────────────┘

SQL Syntax

IS_NOT_NULL(<expr>)

SQL Examples

SELECT IS_NOT_NULL(1);

┌────────────────┐
│ is_not_null(1) │
├────────────────┤
│ true           │
└────────────────┘

15 - IS_NULL

Checks whether a value is NULL.

Analyze Syntax

func.is_null(<expr>)

Analyze Examples

func.is_null(1)

┌─────────────────┐
│ func.is_null(1) │
├─────────────────┤
│ false           │
└─────────────────┘

SQL Syntax

IS_NULL(<expr>)

SQL Examples

SELECT IS_NULL(1);

┌────────────┐
│ is_null(1) │
├────────────┤
│ false      │
└────────────┘

16 - LEAST

Returns the minimum value from a set of values.

Analyze Syntax

func.least((<value1>, <value2> ...))

Analyze Examples

func.least((5, 9, 4))

┌───────────────────────┐
│ func.least((5, 9, 4)) │
├───────────────────────┤
│                     4 │
└───────────────────────┘

SQL Syntax

LEAST(<value1>, <value2> ...)

SQL Examples

SELECT LEAST(5, 9, 4);

┌────────────────┐
│ least(5, 9, 4) │
├────────────────┤
│              4 │
└────────────────┘

17 - NULLIF

Returns NULL if two expressions are equal. Otherwise return expr1. They must have the same data type.

Analyze Syntax

func.nullif(<expr1>, <expr2>)

Analyze Examples

func.nullif(0, null) 

┌──────────────────────┐
│ func.nullif(0, null) │
├──────────────────────┤
│                    0 │
└──────────────────────┘

SQL Syntax

NULLIF(<expr1>, <expr2>)

SQL Examples

SELECT NULLIF(0, NULL);

┌─────────────────┐
│ nullif(0, null) │
├─────────────────┤
│               0 │
└─────────────────┘

18 - NVL

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

Analyze Syntax

func.nvl(<expr1>, <expr2>)

Analyze Examples

func.nvl(null, 'b'), func.nvl('a', 'b')

┌──────────────────────────────────────────┐
│ func.nvl(null, 'b') │ func.nvl('a', 'b') │
├─────────────────────┼────────────────────┤
│ b                   │ a                  │
└──────────────────────────────────────────┘

func.nvl(null, 2), func.nvl(1, 2)

┌────────────────────────────────────┐
│ func.nvl(null, 2) │ func.nvl(1, 2) │
├───────────────────┼────────────────┤
│                 2 │              1 │
└────────────────────────────────────┘

SQL Syntax

NVL(<expr1>, <expr2>)

Aliases

IFNULL

SQL Examples

SELECT NVL(NULL, 'b'), NVL('a', 'b');

┌────────────────────────────────┐
│ nvl(null, 'b') │ nvl('a', 'b') │
├────────────────┼───────────────┤
│ b              │ a             │
└────────────────────────────────┘

SELECT NVL(NULL, 2), NVL(1, 2);

┌──────────────────────────┐
│ nvl(null, 2) │ nvl(1, 2) │
├──────────────┼───────────┤
│            2 │         1 │
└──────────────────────────┘

19 - NVL2

Returns <expr2> if <expr1> is not NULL; otherwise, it returns <expr3>.

Analyze Syntax

func.nvl2(<expr1> , <expr2> , <expr3>)

Analyze Examples

func.nvl2('a', 'b', 'c'), func.nvl2(null, 'b', 'c')

┌──────────────────────────────────────────────────────┐
│ func.nvl2('a', 'b', 'c') │ func.nvl2(null, 'b', 'c') │
├──────────────────────────┼───────────────────────────┤
│ b                        │ c                         │
└──────────────────────────────────────────────────────┘

func.nvl2(1, 2, 3), func.nvl2(null, 2, 3)

┌────────────────────────────────────────────┐
│ func.nvl2(1, 2, 3) │ func.nvl2(null, 2, 3) │
├────────────────────┼───────────────────────┤
│                  2 │                     3 │
└────────────────────────────────────────────┘

SQL Syntax

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

SQL Examples

SELECT NVL2('a', 'b', 'c'), NVL2(NULL, 'b', 'c');

┌────────────────────────────────────────────┐
│ nvl2('a', 'b', 'c') │ nvl2(null, 'b', 'c') │
├─────────────────────┼──────────────────────┤
│ b                   │ c                    │
└────────────────────────────────────────────┘

SELECT NVL2(1, 2, 3), NVL2(NULL, 2, 3);

┌──────────────────────────────────┐
│ nvl2(1, 2, 3) │ nvl2(null, 2, 3) │
├───────────────┼──────────────────┤
│             2 │                3 │
└──────────────────────────────────┘

20 - OR

Conditional OR operator. Checks whether either condition is true.

Analyze Syntax

or_(<expr1>[, <expr2> ...])

Analyze Examples

or_(  
    table.color == 'green',  
    table.shape == 'circle',  
    table.price >= 1.25  
)

SQL Syntax

<expr1> OR <expr2>

SQL Examples

SELECT * FROM table WHERE
    table.color = 'green'
    OR table.shape = 'circle'  
    OR table.price >= 1.25;