Saved Query Definition - SQL

This section of the Saved Query Manager provides the facility to enter Structured Query Language (SQL) statements for defining complex system queries. These queries must match the system data schemas in order to work properly. This page is accessed via Business Process Automation > Power Data > Event Management > Saved Queries. This page appears if you do NOT select the Use In Finder check box and click View/Define Query.

When the business monitor uses a saved query, it modifies the "Find All" SQL by replacing the existing select clause with "select count(*)". This is how the business monitor calculates the total count of objects meeting the criteria defined in the saved query.

There are times when you want to display quantities other than total count. In this case, you can indicate that the SQL statement in the "Find All" should be used "as is" by putting "!!" at the beginning of the text for the "Find All" SQL. In this usage, the SQL statements must have one the following SQL aggregate functions in the select clause to return a quantity which will be displayed in the business monitor.

AVG() - Returns the average value

COUNT() - Returns the number of rows

FIRST() - Returns the first value

LAST() - Returns the last value

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

In short, the "!!" notation should be used only in the saved queries used exclusively for the business monitor and the SQL statements that follow must use a SQL aggregate function in the select cause.

Check One SQL

The SQL statements entered here will be used in conjunction with specific automation agents, events, conditions, and actions. They are intended to search for certain criteria specific to the current object with which the SQL statement is associated. SQL requires one, and only one, bind variable, matching the GID of the record being checked.

For example, you could create a saved query that when used through an automation agent, searches for whether the current shipment you are dealing with has been released yet. The SQL entered would be:

select order_base_gid from ob_ship_unit

where order_base_gid=? and is_processed='N'

Find All SQL

The SQL statements entered here will be used through the Process Manager. Where Check One SQL will only search a limited number of records (in the above example, it will only check if the current shipment you are dealing with has been released), Find All SQL will be used to do a blanket query for all records in the appropriate tables.

For example, by entering the below SQL statement in the Find All SQL field, you could run the same saved query through the Process Manager, but it will do a broader search to find ALL the shipments that have not been released:

select order_base_gid from ob_ship_unit where is_processed='N'