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



Quick links:

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

Did this page help you?