An expression is a combination of one or more values, operators, and built in functions that evaluate to a value.
A simple CCL expression specifies a constant, NULL, or a column. A constant can be a number or a text string. The literal NULL denotes a null value. NULL is never part of another expression, but NULL by itself is an expression.
You can specify a column name by itself or with the name of its stream or window. To specify both the column and the stream or window, use the format "stream_name.column_name."
A compound CCL expression is a combination of simple or compound expressions. Compound expressions can include operators and functions, as well as the simple CCL expressions (constants, columns, or NULL).
You can use parentheses to change the order of precedence of the expression's components.
A conditional CCL expression evaluates a set of conditions to determine its result. The outcome of a conditional expression is evaluated based on the conditions set. In CCL, the keyword CASE appears at the beginning of these expressions and follows a WHEN-THEN-ELSE construct.
The basic structure looks like this:
CASE WHEN expression THEN expression [...] ELSE expression END
The first WHEN expression is evaluated to be either zero or non-zero. Zero means the condition is false, and non-zero indicates that it is true. If the WHEN expression is true, the following THEN expression is carried out. Conditional expressions are evaluated based on the order specified. If the first expression is false, then the subsequent WHEN expression is tested. If none of the WHEN expressions are true, the ELSE expression is carried out.
CASE WHEN mark>100 THEN grade:=invalid WHEN mark>49 THEN grade:=pass ELSE grade:=fail END