SemQL syntax

The SemQL syntax supports the equivalent of SQL expressions, conditions or ORDER BY clauses, which are a combination of one or more values, operators, and functions.

Tokens

Values, operators, and functions are the tokens in the SemQL language.

Values are simple expressions, which include:
- literals are constant values. Numerics are provided as is, other literals must be enclosed in single quotes (e.g., 'John', -42, or '1994-11-07').
- 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 a 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 supports most SQL operators, including arithmetic and character operators (+,-,*,/, ||), comparison operators (=, !=, >, >=, <, ⇐, IN, BETWEEN, LIKE, REGEXP_LIKE, IS NULL), and logical operators (AND, OR, NOT).
Functions and expression constructs combine other tokens to create new expressions.

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, parameter, and variable names) are always case-sensitive.

Case sensitivity

StartYear BETWEEN 2012 and 2014 is equivalent to StartYear Between 2012 AND 2014.
UPPER( CustomerName ) is equivalent to Upper( CustomerName )..
customerName and CustomERName do not represent the same attribute in an entity.

Phrases

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.
The ASC or DESC postfixes define the sort order. The default sort order is ascending.
ORDER BY clauses in the consolidation rules' Ranking Expression also support the NULLS FIRST or NULLS LAST clauses to specify whether null values should be ordered before or after non-null values. By default, the ORDER BY clause uses NULLS LAST if the sort is ASC and NULLS FIRST if the sort is DESC.

Examples of expressions

FirstName is 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.

Examples of conditions

1=1 compares two literals.
Country='USA' compares an attribute and a literal.
Upper(Country) in ('USA', 'US', 'CANADA') uses a function.

Examples of ORDER BY clauses

Country sorts by the Country attribute (ascending by default).
Country DESC sorts by the Country attribute (descending). Null values are sorted (default behavior for DESC) before all non-null values.
Country DESC NULLS LAST sorts by the Country attribute (descending). Null values are sorted after all non-null values.
CASE PublisherID WHEN 'MKT' THEN 1 WHEN 'CRM' THEN 2 ELSE 99 END ASC sorts records where PublisherID equals MKT, then CRM, then the rest.

Comments

Comments in SemQL are surrounded by /* and */.

Code with comments

ANY Contacts HAVE (1=1) /* customer with contacts */
AND
NOT ANY Contacts HAVE ( IsInfluencer = 1 ) /* and no contact is an influencer */