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 TransactSQL syntax that provides a specific service and returns a single value. scalar_function can be builtin scalar functions, such as the SUM, GETDATE, or CAST functions, or scalar userdefined 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 TransactSQL ranking function. 
aggregate_windowed_function  Is any TransactSQL 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 singlevalued 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 nonnullable, the result is typed as nonnullable.
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.