SemQL Syntax
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.
Tokens
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,'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 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 (=
,!=
,>
,>=
,<
,⇐
,IN
,BETWEEN
,LIKE
,REGEXP_LIKE
,IS NULL
) and logical operators (AND
,OR
,NOT
). -
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 2014
is equivalent toStartYear Between 2012 AND 2014
-
UPPER( CustomerName )
is equivalent toUpper( CustomerName )
-
FirstName LIKE 'Unknown%'
is NOT equivalent toFirstName LIKE 'UNKNOWN%'
-
customerName
andCustomERName
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.
TheASC
orDESC
postfix define the sort order. Default sort order is ascending.
Order by clauses in the consolidation rules' Ranking Expression also support theNULLS FIRST
orNULLS LAST
clause to specifies whether NULL values should be ordered before or after non-NULL values. By default, the order by clause usesNULLS LAST
if the sort isASC
andNULLS FIRST
if the sort isDESC
.
-
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.
-
1=1
compares two literals. -
Country='USA'
compares an attribute and a literal. -
Upper(Country) in ('USA', 'US', 'CANADA')
uses a function.
-
Country
sorts by the Country attribute (ascending by default) -
Country DESC
sorts by the Country attribute (descending). Nulls values are sorted (default behavior forDESC
) before all non-null values. -
Country DESC NULLS LAST
sorts 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 ASC
sorts records where PublisherID equalsMKT
, thenCRM
, then the rest.