Introduction to SemQL

SemQL is a language to express declarative rules in the Semarchy Application Builder. This document provides a quick introduction to the SemQL language.

For a detailed description of this language with examples, see SemQL.

SemQL Language Characteristics

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 column names. The code remains implementation-independent.

Qualified Attribute Names

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.

Examples:

  • 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. Record1 and Record2 are predefined qualifiers in the case of a matcher to represent the two records being matched.

SemQL Syntax

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 AND, OR, NOT, IN, IS NULL, LIKE, REGEXP_LIKE, Comparison operators (=, !=, >, >=, <, <=), etc.

  • 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.

SemQL Examples

Enricher Expressions
  • FirstName = InitCap(FirstName)

  • Name = InitCap(FirstName) || Upper(FirstName)

  • City = Replace(Upper(InputAddress.City),'CEDEX','')

In these examples, InitCap, Upper and Replace are SemQL functions. The concatenate operator || is also a SemQL operator.

Validation Conditions

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, AND and OR SemQL operators are used to build the condition.

Matcher

The following binning expression groups customers by their country or postal code:

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

The SemQL editor can be called from the Application Builder when a SemQL expression, condition or clause needs to be built.

The SemQL Editor

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.

  • A description for the selected function or attribute, which appears at the bottom of the editor.

  • The toolbar enables you to indent the code or hide/display the various panels of the editor and to undo/redo code edits.