Differences

This shows you the differences between two versions of the page.

--- docs:2000_concepts:0600_data_retrieval:0100_config_queries [2022/09/01 01:23] – ↷ Links adapted because of a move operation administrator
+++ docs:2000_concepts:0600_data_retrieval:0100_config_queries [2026/07/09 10:22] (current) – aware_admin
@@ Line 1: / Line 1: @@
+{{tag>data data_retrieval query user_interface UI concepts}}
 [<10>]
 ====== 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 ''[[a_f:a:find|FIND]]'' action).
+AwareIM Queries are used to retrieve data from the system using specific search criteria and present results of the search to the user. Therefore AwareIM queries combine search with data presentation. Most queries are defined at the configuration stage (predefined queries, as oppsed to user-defined ones) - a configurator defines them in the Configuration Tool and a user runs them in the Operation Mode. Pre-configured queries can be referenced by rules - FIND. PICK FROM. DISPLAY and other actions of the Rule Language.
+There are three types of queries as far as search criteria is concerned:
+  - Those that use search criteria compatible with the ''[[a_f:a:find|FIND]]'' action (we will be calling them "standard queries")
+  - Queries that use stored prcedures and map to the ''[[a_f:a:exec_sp|EXEC SP]]'' action
+  - Queries that use the SQL SELECT statement and map to the ''[[a_f:a:exec_sql;|EXEC SQL]]''action.
+Standard queries should be suitable for most needs. However, some complex queries may require stored procedures or raw SQL. When configurators create a new query, they specify whether they want a standard query 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 [[docs:2000_concepts:0200_basics:0600_business_object_groups|business object group]]) the instances of which the query will be searching. Then the configurator has 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 (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’:
+When defining a query, the configurator has to indicate the business object, (or [[docs:2000_concepts:0200_basics:0600_business_object_groups|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.
-<code aim> FIND Account WHERE Account.OwnerName = 'John Smith'</code>
+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)
+===== Using Query Builder to Define Standard Queries =====
+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’:
+<code aim>FIND Account WHERE Account.OwnerName = 'John Smith' </code>
 One can define multiple conditions for a query – the conditions may be linked with the ''AND'' or ''OR'' keywords.
@@ Line 12: / Line 27: @@
 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:
-<code aim> FIND Account WHERE Account.Owner.Name = 'John Smith' </code>
+<code aim>FIND Account WHERE Account.Owner.Name = 'John Smith'  </code>
 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.
@@ Line 25: / Line 39: @@
 <callout type="primary" icon="true" title="note"> It is possible to define a query with conditions that use attributes of a business object that the query is not searching for (they are also called //dynamically resolved// attributes or simply //dynamic// attributes). Consider the following query as an example:
-<code aim>FIND Account WHERE Account.OwnerName = Customer.Name</code>
+<code aim>FIND Account WHERE Account.OwnerName = Customer.Name </code>
-The business object Customer used in a query condition is not the object that the query is searching for. //**Aware IM**// replaces references to such attributes with the actual values at run time – just before the search is performed. For example, if the value of the Name attribute of the Customer object is ‘John Smith’ the actual query performed by //**Aware IM**// is:
+The business object Customer used in a query condition is not the object that the query is searching for. //**AwareIM**// replaces references to such attributes with the actual values at run time – just before the search is performed. For example, if the value of the Name attribute of the Customer object is ‘John Smith’ the actual query performed by //**AwareIM**// is:
-<code aim>FIND Account WHERE Account.OwnerName = 'John Smith'</code>
+<code aim>FIND Account WHERE Account.OwnerName = 'John Smith' </code>
-Where does //**Aware IM**// find the instance of the Customer object and what happens if there are no instances of this object or if there are more than one instance? The answer to the first part of the question is that the instance is taken from the Context (this is explained in the [[docs:2000_concepts:0800_data_processing:0100_rule_evaluation:0800_context_rule_execution:0300_other_uses_context|Other Usages of Context]] section); the answer to the second part is that if //**Aware IM**// cannot find the instance of the object in the Context it logs a run-time error and the query is not run. The queries using dynamic attributes should only be used in situations where such attributes can be unambiguously resolved.
+Where does //**AwareIM**// find the instance of the Customer object and what happens if there are no instances of this object or if there are more than one instance? The answer to the first part of the question is that the instance is taken from the Context (this is explained in the [[docs:2000_concepts:0800_data_processing:0100_rule_evaluation:0800_context_rule_execution:0300_other_uses_context|Other Usages of Context]] section); the answer to the second part is that if //**AwareIM**// cannot find the instance of the object in the Context it logs a run-time error and the query is not run. The queries using dynamic attributes should only be used in situations where such attributes can be unambiguously resolved.
 See also [[docs:2000_concepts:0600_data_retrieval:0110_queries_user_input|Queries that Require User Input]]
 </callout>
+===== Defining Queries Implemented as Stored Procedures=====
+As explained before, queries implemented as stored procedures refer to a particular stored procedure defined in the AwareIM database. When such queries execute, **AwareIM** automatically executes the related stored procedure providing values of parameters if required. The results of the stored procedure are then presented to the user as defined by the query - either as a grid, a calendar or a chart. The query follows the format of the  ''[[a_f:a:exec_sp|EXEC SP]]'' action. Some examples are described below:
+<code>EXEC_SP 'AllCustomers' RETURN Customer</code>
+This query is implemented as a stored procedure called "AllCustomer". The stored procedure does not require any parameters and return results that are compatible with the structure of the Customer object (the stored procedure returns records of the table that the Customer object is mapped to in the database).
+<code>EXEC_SP 'TESTWITHPARAM' WITH 'ProspState'=ParamObject.State, 'ProspName'='Julie%' RETURN Prospect</code>
+This stored procedure has two parameters - the second is hardcoded and the first one is initialized from the object in Context (ParamObject).
+<callout type="primary" icon="true" title="note">
+When a configurator defines a new **AwareIM** query implemented as stored procedure and the stored procedure already exists, a developer can initialize the new query from the list of stored procedures available in the database and generate an appropriate EXEC_SP statement.
+</callout>
+<callout type="primary" icon="true" title="note">
+**AwareIM** automatically provides support for dynamic paging, dynamic sorting and dynamic filtering of records returned by queries implemented as stored procedures. However, you need to know the following:
+  - Support for dynamic paging, sorting and filtering is only provided for MySQL and SQL Server databases.
+  - If a stored procedure has multiple SELECT statements, **AwareIM** needs to know which one of the SELECT statements returns records compatible with the RETURN statement of the stored procedure. To do this, enclose this SELECT statement with BEGIN MAIN SELECT and END MAIN SELECT comments, for example:
+%%-- BEGIN MAIN SELECT%%\\
+%%SELECT...%%\\
+%%-- END MAIN SELECT%%\\
+</callout>
+===== Defining Queries Implemented as SQL SELECT Statements =====
+Queries  implemented as SQL SELECT statements should just include the SELECT statement in the query textual view. The SQL table that the SELECT statement operates with should be the table that stores records of a business object that the query queries on. Before AwareIM runs such a query it replaces any tag expressions used in the SELECT statement with values from Context, so the SELECT statement is allowed to include tag expressions. For example:
+<code>SELECT * FROM Customer WHERE Name=<<ParamObject.Name>></code>

data data retrieval query user interface ui concepts