Expressions#

Is a combination of symbols and operators that the Querona Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.

Syntax#

{ constant | scalar_function | [ table_name. ] column | variable
    | ( expression ) | ( scalar_subquery )
    | { unary_operator } expression
    | expression { binary_operator } expression
    | ranking_windowed_function | aggregate_windowed_function
}

Arguments#

Term

Definition

scalar_function

Is a unit of Transact-SQL syntax that provides a specific service and returns a single value. scalar_function can be built-in scalar functions, such as the SUM, GETDATE, or CAST functions, or scalar user-defined functions.

constant

Is a symbol that represents a single, specific data value.

[ table_name. ]

Is the name or alias of a table.

column

Is the name of a column. Only the name of the column is allowed in an expression.

variable

Is the name of a variable or parameter.

( expression )

Is any valid expression as defined in this topic. The parentheses are grouping operators that make sure that all the operators in the expression within the parentheses are evaluated before the resulting expression is combined with another.

( scalar_subquery )

Is a subquery that returns one value. For example: SELECT MAX(UnitPrice) FROM Products

{ unary_operator }

Unary operators can be applied only to expressions that evaluate to any one of the data types of the numeric data type category. Is an operator that has only one numeric operand:

+

indicates a positive number.

-

indicates a negative number.

~

indicates the one’s complement operator.

{ binary_operator }

Is an operator that defines the way two expressions are combined to yield a single result. binary_operator can be an arithmetic operator, the assignment operator (=), a bitwise operator, a comparison operator, a logical operator, the string concatenation operator (+), or a unary operator.

ranking_windowed_function

Is any Transact-SQL ranking function.

aggregate_windowed_function

Is any Transact-SQL aggregate function with the OVER clause.

Expression Results#

For a simple expression made up of a single constant, variable, scalar function, or column name: the data type, collation, precision, scale, and value of the expression is the data type, collation, precision, scale, and value of the referenced element.

When two expressions are combined by using comparison or logical operators, the resulting data type is Boolean and the value is one of the following: TRUE, FALSE, or UNKNOWN.

When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.

Complex expressions made up of many symbols and operators evaluate to a single-valued result. The data type, collation, precision, and value of the resulting expression is determined by combining the component expressions, two at a time until a final result is reached. The sequence in which the expressions are combined is defined by the precedence of the operators in the expression.

CASE Expressions#

The CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is similar to case and if/then/else statements in other languages.

The CASE expression has two formats:

  • The simple CASE expression compares an expression to a set of simple expressions to determine the result.

  • The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Syntax#

Simple CASE expression:

CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Searched CASE expression:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Arguments#

input_expression Is the expression evaluated when the simple CASE format is used. input_expression is any valid expression.

WHEN when_expression Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expression Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result_expression is any valid expression.

ELSE _else_result_expression* Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Return Types#

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Return Values#

Simple CASE expression:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

  • Allows only an equality check.

  • In the order specified, evaluates input_expression = when_expression for each WHEN clause.

  • Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

  • If no input_expression = when_expression evaluates to TRUE, the Querona Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE expression:

  • Evaluates, in the order specified, Boolean_expression for each WHEN clause.

  • Returns result_expression of the first Boolean_expression that evaluates to TRUE.

  • If no Boolean_expression evaluates to TRUE, the Querona Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

COALESCE#

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Syntax#

COALESCE ( expression [ ,...n ] )

Arguments#

expression Is an expression of any type.

Return Types#

Returns the data type of expression with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.

NULLIF#

Returns a null value if the two specified expressions are equal.

Syntax#

NULLIF ( expression , expression )

Arguments#

expression Is any valid scalar expression.

Return Types#

Returns the same type as the first expression.

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.