The erSQL language provides a user friendly access to the database. The query language borrowed a few syntactical constructs from (relational) SQL, but is completely different in structure:

• SQL refers to the (logical / physical) data model and is primarily used by specialists
• erSQL refers to the conceptual data model and is designed for users and specialists

To create an erSQL query - use the visual query builder:

• Start with an entity-type and selection of the access key
• Click through the path from the start entity to all affected entities
• Select the desired attributes
• Finetune the query with where-clauses and order by
• Execute the query to show the results in a table / export to Excel

 Done.

A simple example

Customer NickNameInc collects so called compound orders on behalf of customers. The goods must be delivered to NickNameInc in bulk to reduce shipping cost.
The payments however are paid directly by the individual customers. If the total amount due has not been paid, the company sends a report to NickNameInc. It is their job to chase the outstanding payments. A report is required which shows all orders which are due (status D) and all payments received from the customers for order of NickNameInc, including the names of these customers.

A more elaborate Example

This picture is an extract from a screen-shot (the full picture can be seen below) of the graphics-plugin and query-plugin of the metaEditor. The graphics pane displays a section of the model. After clicking of the 'Order'-Box (see mouse-pointer) the system highlights this box and all boxes which are connected by a relation.

To create such a query take the conceptual model and walk alonge the path which links the entities and tick off the attributes you would like to have in the result-set. (A graphical query builder is on the development list)

01 The query-text starts with the select statement which is followed by the attribute names. The names are constructed from identifier.attribute-name. The identifier 'C' was selected insted of 'Customer' to keep the text short.

05 Cat.*name refers to the primary key (all system names start with an asterisk). 'as Category' specifies that this text should be used to indentify the attribute in the result-set instead of the selection-name.

06 where clause to define conditions on the result-set.. O.DateShipped ist compared with '2009-10-10' - the standard date format. Note that the query return an object of type 'Date' and is converted to string only for the purpose of the comparison or the display. A program which issues a query-command receives objects and can use them according to their type.

07 O.Status.. can be referenced in the where-clause despite the fact that it is not part of the resul-set. It is an attribute of the entity order - you can see that in the model.

08 Order by ... specifies the ordering sequence.

09 start with type = 'Customer', name = 'wild card' e.g. 'A*'.  'as C' defines the identifier for the select statement.

10 follow 'places' to type 'Order' as O; name the relation by its name and the target entity-type.

11 to 12 are faily obvious:

13 is somewhat special because here the backward relation is used.

The Query-View

The erSQL-Query is used in this example to extract data and to display them in tabular form.

The result can the be exported to an Excel-Sheet for further processing / calculation or simulation.

The same query can be used to create reports with BIRT: It is easy to collect data across a long path and to create a CUBE in BIRT. Such a cube can be used to analyse the data and create custom reports in various formats.

(to entlarge click on the picture)