Start Free Trial   Support

Expressions

It is a combination of symbols and operators that the Lyftron 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
constant Is a symbol that represents a single, specific data value.
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.
[ 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 Lyftron 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 Lyftron 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.