The DEFINE statement allows you to simplify complicated queries by creating identifiers as synonyms for queries. Defining a query will not cause it to be performed. The query is executed only if it is used inside another active query statement. Queries can be nested in this manner, allowing you to define a query once and reuse it later.
In the following example, the first statement defines a query named cheapProducts. The second statement, a SELECT statement, is actually executed. It uses the query defined in the first statement in its FROM clause. This causes the result-set of the first query to be used as the input to the second query.
DEFINE cheapProducts AS
FROM ProductExtent AS product
WHERE product.unitPrice < 1.0;
FROM s IN nineties
WHERE cheapProducts.NAME LIKE "Bug*"
In the above example, a query named cheapProducts is first defined and then used as input to the second query. This query returns the products with a title beginning with "Bug" that cost less that 1.00.
Compare with the following:
FROM product IN ProductExtent
WHERE product.NAME LIKE "Bug*"
The above query will return all products with a name beginning with "Bug".