Introduction to SemQL
SemQL is a language to express declarative rules in Semarchy Application Builder. This document provides a quick introduction to the SemQL language.
|For a detailed description of this language with examples, refer to SemQL .
The SemQL Language has the following characteristics:
The syntax is similar to the SQL language and most SemQL functions map directly to database functions.
SemQL is converted on the fly and executed by the hub database.
SemQL uses Qualified Attribute Names instead of columns names. The code remains implementation-independent.
A Qualified Attribute Name is the path to an attribute from the current entity being processed.
This path not only allows accessing the attributes of the entity, but also allows access to the attributes of the entities related to the current entity.
FirstName: Simple attribute of the current Employee entity.
InputAddress.PostalCode: Definition Attribute (PostalCode ) of the InputAddress Complex Attribute used in the current Customer entity.
CostCenter.CostCenterName: Current Employee entity references the CostCenter entity and this expression returns an employee’s cost center name
CostCenter.ParentCostCenter.CostCenter: Same as above, but the name is the name of the cost center above in the hierarchy. Note that ParentCostCenter is the referenced role name in the reference definition.
Record1.CustomerName: CustomerName of the first record being matched in a matcher process.
Record2are predefined qualifiers in the case of a matcher to represent the two records being matched.
The SemQL Syntax is similar to the SQL language for creating expressions , conditions and order by clauses.
Expressions contain functions and operators, and return a value of a given type.
Conditions are expressions returning true or false. They support
REGEXP_LIKE, Comparison operators (
Order By Clauses are used to sort records in a set of results using an arbitrary combination of Expressions sorted ascending or descending
In expressions, conditions and order by clauses, it is possible to use the SemQL functions. The list of SemQL functions is provided in the SemQL Editor
|SemQL is used to create expressions, conditions and order by clauses. SELECT, UPDATE or INSERT queries are not supported, as well as joins, sub-queries, aggregates, in-line views and set operators.
InitCap(FirstName) || Upper(FirstName)
In these examples,
Replace are SemQL functions.
The concatenate operator
|| is also a SemQL operator.
The following condition checks the quality of the InputAddress complex attribute of the customer entity:
InputAddress.Address is not null and ( InputAddress.PostalCode is not null or InputAddress.City is not null)
In this example, the
IS NOT NULL,
OR SemQL operators are used to build the condition.
The following binning expression groups customers by their Country/PostalCode:
InputAddress.Country || InputAddress.PostalCode
The following matching condition matches two customer records by name, address and city name similarity:
SEM_EDIT_DISTANCE_SIMILARITY( Record1.CustomerName, Record2.CustomerName ) > 65
and SEM_EDIT_DISTANCE_SIMILARITY( Record1.InputAddress.Address, Record2.InputAddress.Address ) > 65
and SEM_EDIT_DISTANCE_SIMILARITY( Record1.InputAddress.City, Record2.InputAddress.City ) > 65
In this last example,
SEM_EDIT_DISTANCE_SIMILARITY is a SemQL function. Record1 and Record2 are predefined names for qualifying the two records to match.
The SemQL editor can be called from the Application Builder when a SemQL expression, condition or clause needs to be built.
This editor is organized as follows:
Attributes available for the expression appear in the left panel. Double-click an attribute to add it to the expression.
Functions declared in SemQL appear in the left bottom panel, grouped in function groups. Double-click a function to add it to the expression. You can declare your own database functions in the model so that they appear in the list of functions.
Messages appear in the right bottom panel, showing parsing errors and warnings.
Description for the selected function or attribute appear at the bottom of the editor.
The Toolbar enables you to to indent the code or hide/display the various panels of the editor and to undo/redo code edits.