Configuring Queries

Queries can be defined at the configuration stage - a user can run such pre-configured queries in the Operation Mode. Pre-configured queries can also be referenced by rules (see FIND action). Queries can be implemented either by the FIND or EXEC SP or EXEC SQLactions of the Rule Language. The standard query that uses the FIND action should be suitable for most needs. However, some complex queries may require raw SQL. In this case a query can refer to a stored procedure or include a block of raw SQL that uses the SELECT SQL statement. When configurators create a new query, they specify whether they want a standard query that uses the FIND action, a stored procedure or an SQL SELECT statement. The concept of a query in AwareIM includes both data search criteria and a particular presentation of the found data to the user (grid, calendar, chart, tree etc). Irrespective of the implementation the options for data presentation are for the most part the same - whether it's a standard query, a stored procedure or raw SQL.

When defining a query, the configurator has to indicate the business object, (or business object group) the instances of which the query will be searching. For standard queries the configurator has to either select the business object or group explicitly (if working in the Query Builder View) or provide it as part of the FIND action (if working with the Query Text View). For queries implemented as stored procedure the user has to specify the RETURN statement followed by the name of the business object or group. For queries implemented as an SQL SELECT statement, the name of the business object can be derived from the name of the table in the SELECT statement of the SQL.

The next step when defining a query is to indicate the conditions of search – only those instances of the business object that have attribute values matching the specified conditions will be retrieved by the query. for queries implemented as stored procedures or SQL SELECT statements, this step is done inside the stored procedure or SQL itself. For standard queries conditions of the search can be specified using Query Builder View as explained below or as part of the FIND action (if working in the Textual View)

For standard queries conditions of the search can be specified using the Query Builder. If conditions are not specified all instances of the business object will be retrieved. For example, if a query is to retrieve accounts of John Smith, the query has to include a condition that compares the value of the OwnerName attribute with ‘John Smith’:

FIND Account WHERE Account.OwnerName = 'John Smith' 

One can define multiple conditions for a query – the conditions may be linked with the AND or OR keywords.

Note that query conditions allow specifying not only attributes of the business object that the query will be searching, but also attributes of the related objects. For example, if account owner is a Customer object related to the Account object via the Owner reference attribute, the above query could be specified as follows:

FIND Account WHERE Account.Owner.Name = 'John Smith'  

It is also possible to indicate attributes of objects related to the related objects, for example, Account.Owner.Company.Name. There is no limit on the level of nesting of the related objects.

The configurator may indicate that instances of a business object found by a query should be sorted by a particular attribute(s) in a particular order and also indicate how these instances should be displayed in the Operation Mode – see Data Presentation.

Configuration of queries is described in more detail in the Adding/Editing Queries section.

FIND Account WHERE Account.OwnerName = Customer.Name 

FIND Account WHERE Account.OwnerName = 'John Smith'