RAQL Operators

You may use the standard arithmetic operators in RAQL expressions: + , - , * or / .

Note: The division operator uses integer division unless the datatype of the data is decimal. You can set the datatype using casting functions or by additing datatype information in a schema. See Built-In Plain Functions and Providing Dataset Path and Datatype Information in a Schema for more information.

You may use the following comparison operators in Where clauses:

like: for matching text based on a pattern. Use % to indicate zero to any number of characters. For example: where lastname like 'A%' to find any last name starting with a capital A.

in: to match values against an enumerated set of values defined within brackets. For example: where direction in ("N","NW","W")

The literal values or other expressions being compared must be the same basic datatype. In many cases, this may require that values or expressions be cast to an appropriate type for a successful comparison. See Literal Values in Conditions or Expressions for additional information.

Text comparison with the mathematic comparison operators is not case sensitive. Text comparisons to patterns or enumerations are case sensitive.

You may use the following logical operators in Where clauses:

Operators are not case sensitive. You can use parentheses to build complex logical expressions such as: where (service_level in ("gold","silver")) and (rating >= 2.0 or overdue < 100 ).

You may use the || string operator to concatenate strings or the concat function.

The first form selects a result expression based on different values of a case expression. Its syntax is

case expression when expression then expression [,
when expression then expression ]...
[else expression]
end

case status when 0 then 'success'
when 1 then 'fail'
else 'unknown'
end

The second form selects a result expression based on a boolean expression. Its syntax is

case when booleanExpression then expression [,
when booleanExpression then expression ]...
[else expression]
end

The following example is equivalent to the above example, but uses the second form.

case when status=0 then 'success'
when status=1 then 'fail'
else 'unknown'
end