JoSQL User Manual - Execute On Clause

Execute On Clause

"EXECUTE ON" is an extension to the SQL syntax that allows the developer to specify a number of functions that are executed/evaluated at specific points within the statement execution (see Execution) process.

This serves 2 purposes:

Allows functions to be executed once on a specific set of records at a specific time to allow the results to be "saved" in the Query for later use, perhaps in the query itself.
Allows aggregate functionality to be mixed with standard SQL functionality.

The general syntax for the EXECUTE ON clause is:

EXECUTE ON ALL | RESULTS | GROUP_BY_RESULTS Expression [ , Expression ]* [ [ AS ] Alias ]

The "EXECUTE ON" clause should be placed at the END of the sql query, after any LIMIT clause.

For example:

SELECT *
FROM   java.io.File
WHERE  length (name) > @avg_length
EXECUTE ON ALL avg (:_allobjs, length) AS avg_length

The "EXECUTE ON ALL" will cause the functions to be executed on ALL the java.io.File objects BEFORE processing of the WHERE clause begins. If an "alias" is provided then this is used as the key for the save value in the Query. Thus after the query has been executed the average length of the files would be available by calling:

q.getSaveValue ("avg_length");

on the Query object.

Also note that the @ save value syntax has been used in the WHERE clause.

The "EXECUTE ON RESULTS" should be used when you wish to execute a number of functions on the objects gained from processing the WHERE clause. For example:

SELECT *
FROM   java.io.File
WHERE  length (name) > 20
EXECUTE ON RESULTS avg (:_allobjs, length) AS avg_length

And, as according to the execution scope (see Execution), the :_allobjs special bind variable will contain the objects that were matched in the WHERE clause. Note: because the "EXECUTE ON RESULTS" functions are only executed AFTER the WHERE clause has been executed using any save values in the WHERE clause will cause incorrect results, for example:

SELECT *
FROM   java.io.File
WHERE  length (name) > @avg_length
EXECUTE ON RESULTS avg (:_allobjs, length) AS avg_length

Will not cause any matches to be found in the WHERE clause since the avg_length save value will be null (however, if the save value is specified by the user prior to execution of the Query then the WHERE clause will limit the set of objects found).

The EXECUTE ON GROUP_BY_RESULTS should be used when you wish to execute a number of functions on each of the GROUP BY results. The functions will be executed for each GROUP BY "key". And, as according to the execution scope (see Execution), the :_allobjs special bind variable will contain the objects that were matched in the WHERE clause for the particular group by key.

SELECT *
FROM   java.io.File
GROUP BY parent
EXECUTE ON GROUP_BY_RESULTS avg (:_allobjs, length) AS avg_length

In this case for each unique directory the function avg will be executed on the matching files (files in the directory). To prevent save value namespace clashing each save value is saved against the GROUP BY "key" (i.e. the parent file name).