The SemQL syntax supports the equivalent of SQL Expressions, Conditions or Order By Clause, which are a combination of one or more Values, Operators, and Functions.
Values, operators and functions are the tokens in the SemQL language.
Values are simple expressions, which include:
literals are constant values. Numeric are provided as is, other literals must be surrounded by single quotes
'. For example,
Attributes refer to attributes of the entities in the model.
Model Variables contain values that are used to customize the user experience or parameterize an integration job.
Search Parameters store the values entered in search form and submitted to the search condition attached to the search form. Search parameters are available only in their own search form’s condition.
Operators modify, combine or compare expressions. SemQL support most SQL operators, including arithmetic and character operators (
||), comparison operators (
IS NULL) and logical operators (
Functions & Expression Constructs combine other tokens to create new expressions.
Note that case-sensitivity differs for the language tokens:
Operators are not case-sensitive.
Functions are not case-sensitive for Oracle and PostgreSQL, but are case-sensitive for SQL Server.
Values (attribute, parameters and variables names) are always case-sensitive.
StartYear BETWEEN 2012 and 2014is equivalent to
StartYear Between 2012 AND 2014
UPPER( CustomerName )is equivalent to
Upper( CustomerName )
FirstName LIKE 'Unknown%'is NOT equivalent to
FirstName LIKE 'UNKNOWN%'
CustomERNamedo not represent the same attribute in an entity.
Expressions, Conditions and Order By Clauses are the phrases supported by the SemQL language.
Expressions combine values, operators and functions to return a non-boolean value.
Conditions combine values, operators and functions to return a boolean value (true or false).
Order By Clauses are expressions used to sort data by ascending or descending values of the expression.
DESCpostfix define the sort order. Default sort order is ascending.
Order by clauses in the consolidation rules' Ranking Expression also support the
NULLS LASTclause to specifies whether NULL values should be ordered before or after non-NULL values. By default, the order by clause uses
NULLS LASTif the sort is
NULLS FIRSTif the sort is
FirstNameis an attribute.
'USA'is a string literal.
Initcap(FirstName)is a function using an attribute.
Initcap(FirstName) || ' ' || Initcap(LastName)is a combination of operators, functions, attributes and literals.
1=1compares two literals.
Country='USA'compares an attribute and a literal.
Upper(Country) in ('USA', 'US', 'CANADA')uses a function.
Countrysorts by the Country attribute (ascending by default)
Country DESCsorts by the Country attribute (descending). Nulls values are sorted (default behavior for
DESC) before all non-null values.
Country DESC NULLS LASTsorts by the Country attribute (descending). Nulls values are sorted after all non-null values.
CASE PublisherID WHEN 'MKT' THEN 1 WHEN 'CRM' THEN 2 ELSE 99 END ASCsorts records where PublisherID equals
CRM, then the rest.