Logic, Flow, and Comparison

Logic functions and operators help to control the flow of operations. Booleans are not directly supported:

• true = any non-zero value
• false = zero

Flow

if

if allows for two separate logic paths to be followed depending on whether the condition evaluates to true(non-zero) or false (zero)

Parameters:

• condition - a UInt that is the test.
• then - what to do if condition is non-zero
• else - what to do if condition is zero.

Then and else can be null.

Usage:

• function call:if(condition, then, else)
• operator:condition ? then : else

Returns:

• then if the condition evaluates to true
• else if the condition evaluates to false

multiIf

multiIf allows for more than one condition to be checked, in effect making it a CASE statement. It is composed of multiple conditions that are checked in order. If no conditions are met, then else is returned.

Parameters:

• condition_N - a UInt that a the test.
• then_N - what to do if condition_N is non-zero, or true.
• else - what to do if all conditions are zero, or false

Then and else can be null.

Usage:

• multiIf(condition_1, then_1, condition_2, then_2, ...condition_N, then_N,, else)

Returns:

• then_N for the first condition in the function that returns true
• else if all conditions evaluate to false

sleep, sleepEachRow

sleep and sleepEachRow pause the query for num_seconds number of seconds after processing each row.

Parameters:

• num_seconds - the number of seconds to sleep after processing each row.

Usage:

• sleep(num_seconds)
• sleepEachRow(num_seconds)

Returns:

• no return value, just pauses query execution.

throwIf

throwIf throw an exception if a condition is met.

Parameters:

• condition - the condition to evaluate. If it evaluates to 1, or true, then error_message is returned inside of a DB::Exception.
• error_message - the text the exception should return

Usage:

• throwIf(condition, error_message)

Returns:

• no return value. Either throws an exception or does not.

Comparison

between

BETWEEN determines if x's value is between a and b. It is an alternative to using syntax like:

• x <= a AND x >= b
• x < a OR x > b

Usage:

• x BETWEEN a AND b
• x NOT BETWEEN a AND b

Returns:

• true or 1 if both arguments are non-zero
• false or 0 in all other scenarios

equals, notEquals

Determines if two values are equal. Equals can be used on any data type, however, both values must be of the same data type.

• function call: equals(x,y), notEquals(x,y), not equals(x,y)
• operator: x = y, x == y, x != y, x<>y

Returns:

• true or 1 if the arguments are equal
• false or 0 if the arguments are not equal

greater, greaterOrEquals

Determines if one value is greater/> or greaterOrEquals/>= than another

• function call: greater(x,y), greaterOrEquals(x,y)
• operator: x > y, x >= y

Returns:

• true or 1 if x is greater/greaterOrEqual to y
• false or 0 otherwise

greatest

greatest returns the larger of two number values.

Parameters:

• value_1 - the first value
• value_2 - the second value

Usage:

• greatest(value_1, value_2)

Returns:

• The larger of the two number values.

isFinite

isFinite determines if a number is equal to +inf or -inf.

Parameters:

• value - the value to compare to inf

Usage:

• isFinite(value)

Returns:

• 0, or false if the number is not equal to +inf or -inf.
• 1, or true if the number is equal to +inf or -inf.

isInfinite

isInfinite determines if a number is equal to +inf or -inf. This funtion is the inverse of isFinite

Parameters:

• value - the value to compare to inf

Usage:

• isInfinite(value)

Returns:

• 0, or false if the number is equal to +inf or -inf.
• 1, or true if the number is not equal to +inf or -inf.

isNaN

isNaN compares a value to NaN.

Parameters:

• value - the value to compare to NaN

Usage:

• isNaN(value)

Returns:

• 0, or false if the number is NOT EQUAL to NaN
• 1, or true if the number IS EQUAL to NaN

least

least returns the smaller of two number values.

Parameters:

• value_1 - the first value
• value_2 - the second value

Usage:

• leastst(value_1, value_2)

Returns:

• The smaller of the two number values.

less, lessOrEquals

Determines if one value is less/< or lessOrEquals/<= than another

• function call: less(x,y), lessOrEquals(x,y)
• operator: x < y, x <= y

Returns:

• true or 1 if x is less/lessOrEqual to y
• false or 0 otherwise

like

Determines if the string is equal to the regular expression given as an argument.

• function call: like(string,regular_expression)
• operator: string LIKE regular_expression

Returns:

• true or 1 if the string matches regular_expression
• false or 0 otherwise

Logic

and

Usage:
Logical AND operation

• function call: and (x,y)
• operator: x AND y

Returns:

• true or 1 if both arguments are non-zero
• false or 0 in all other scenarios

not

Returns the opposite 'boolean' value of the argument

• function call: not(x)
• operator: NOT x

Returns:

• true or 1 if x is zero
• false or 0 if x is non-zero

or

Usage:
Logical OR operation

• function call: or (x,y)
• operator: x OR y

Returns:

• true or 1 if either argument is non-zero
• false or 0 if both arguments are zero

xor

Usage:
Logical XOR operation

• function call: xor (x,y)

Returns:

• true or 1 if exactly one argument is true or 1
• false or 0 otherwise