Define Advanced Queries

The Advanced Query element allows you to execute more complex query statements or any other SQL statements. This element can be used to manage your entities or to execute any other statement in the database.

An advanced query can have:

Input parameters: You might need to use a parameter in your query. You manage input parameters in the Advanced query editor. How?
Output parameter: The output parameters of an advanced query are the record list (List output parameter) and the query count (Count output parameter) that you can manage in the Expression or Variable editors.

The type of the elements in the record list is always a Structure defined in the Advanced query editor. How?

The query count has the total number of records returned by the query without any Max. Records limitation.

Execute an Advanced Query

To execute an advanced query, simply drag the Query tool to your Action flow (Collection of elements that graphically implements the behavior of an action. You can have assign and control elements, exception handlers or you can invoke other actions. The elements are linked through Connectors.) and launch the Advanced query editor, where you explicitly type in the SQL statements. Any SQL statement is possible, as long as you have the database permissions to execute them. Service Studio performs some validations to the SQL you type here, based on the Database eSpace property, and if the SQL is not compliant, a warning message is presented and you should fix your statements.

You cannot use entities with prefixes that correspond to the internal tables, namely oslog_, ossys_, and osusr_. If you use any of these prefixes, Service Studio presents an error message and the eSpace is not valid (An eSpace is valid when all expressions are correctly defined and the TrueChange operation issues no errors.).

When there is integration with external systems, transaction handling might need some extra care. Why?

You may force the advanced query to be executed in a specific database. How?

You may execute existing Advanced Queries using the Refresh Query element. Learn more About Refreshing a Query.

Query Time-out

The Query time-out is the maximum time Platform Server waits for this query to be executed, from the time the database connection was established.

By default, the query time-out is 30 seconds, but you can adjust this value to your needs. You can change the Query time-out for all queries or for just one query, as follows:

Global for all queries: contact the Service Center administrator in order to adjust, in the Platform Server Configuration Tool, the Default query timeout parameter at the Database tab.
Specific to a query: define the Timeout in Seconds property with the suitable value. How?

Improving Runtime Performance

To improve the runtime performance of your Advanced Queries, Service Studio provides you with the following advanced techniques:

Cache contents: Cache the contents of an Advanced Query. How?