# OQL

This is from the java blend doc, that follows OQL

The Java Blend tool supports OQL, the ODMG Object Query Language. However, the Java Blend tool in some aspects differs from the ODMG standard. These differences are noted where appropriate. For a complete summary of OQL, see The Object Database Standard: ODMG 2.0,by R.G.G. Cattell et al (Morgan Kaufmann Publishers, Inc., 1997). In particular, refer to Chapter 4, "Object Query Language". The URL for the ODMG standard is http://www.odmg.org.

Applications use queries to access persistent Java objects. The Java Blend mapping tool generates a set of query files. You can modify these generated queries. You can also write your own queries. This chapter focuses on the OQL syntax and shows you how to write your own queries. This chapter includes the following sections:

Java Blend Generated Queries
  Describes the default queries that the Java Blend tool generates for you.
Writing Your Own Queries
  Discusses the basics for writing your own queries
OQL Queries
  Describes the OQL syntax that you need to write most queries.
Advanced OQL
  Describes advanced features of OQL.
OQL Syntax and Grammar
  Lists the complete syntactical definition for OQL in Extended Backus Naur Form (EBNF).

## Java Blend Generated Queries

In DB->Java mode, the Java Blend mapping tool generates query files, one for each class, at the same time that it generates source files in the Java programming language. By default, it places these query files in the same directory as the source files. A separate file exists for each query. A generated query file name consists of the prefix OQL, followed by a descriptive query class name and the .oql extension. For example, the name of the default generated query file for a Customer class is OQLGetAllCustomer.oql.

The generated default query simply returns all objects of the class. Thus, for a customer class, the default query returns all customers in the database that are specified by the customer mapping. You can edit these query files to be more selective.

Note: The examples that follow show OQL reserved words in uppercase. You can use either uppercase or lowercase for OQL reserved words. However, mixed case is not allowed. For example, you can use select or SELECT, but you cannot use Select.

An example of a default generated query to get all customers, such as OQLGetAllCustomer.oql, is:

    import demo.dbo.Customer;

    SELECT o FROM Customer o;

## Writing Your Own Queries

Use OQL to write your own queries. If you like, you can use the default generated queries as models for your own queries.

OQL syntax follows the Java programming language expression syntax. It enables you to easily specify a query using Java programming language classes. OQL is also similar to the query portion of SQL-92, and it relies on the ODMG object model.

Although the syntax of OQL closely resembles that of SQL-92, the two languages are based on different models. SQL is based on the relational model, in which queries access tables, columns, and rows. In contrast, OQL is based on an object model, in which queries access classes, objects, attributes, relationships, and methodsjust as programs written in the Java programming language do.

In OQL queries, the schema of the underlying database is invisible; OQL users have no direct access to database tables, columns, rows. For example, if a database has an Orders table with a foreign key relationship to a Customer table, the following OQL query selects an order for a specific customer:

    SELECT o FROM Orders o

    WHERE o.customer.name = "ABC Company"

Unlike this OQL query, the equivalent SQL query must define the join condition between the Orders table and the Customer table:

    SELECT o.no, ... FROM Orders o, Customer c

    WHERE c.name = "ABC Company" AND c.cid = o.cid

As the OQL query in the preceding example shows, writing an OQL query is much simpler than performing the same query using the Java programming language. With just a few English-like phrases, you can quickly construct a powerful query. To do the equivalent in an application requires more sophisticated programming knowledge and more lines of code.

### Query Development Process

The Java Blend tool lets you write both static queries and dynamic queries. Static queries are compiled during application development; dynamic queries are compiled and executed when your application runs. See "Understanding Queries" for more information about creating and using static and dynamic queries.

A Java application uses a query resource file to obtain a collection of objects from the database. The application creates an instance of a generic query object, opens the resource file, and runs the query. The query returns all matching values or objects as a collection. The application can then use the objects in this collection according to its needs.

### Adding Business Logic to Queries

Queries are the principal means by which an application initially retrieves the objects that it needs. Later, it is quite common for applications to navigate between related objects. Remember to construct queries that enable applications to get these objects in accordance with your business logic. Keep the following things in mind when you design your queries:

- The nature and purpose of the applications using these queries
- The objects that these applications need
- The best way for these applications to access these objects

In addition, you should coordinate your queries with the methods and members in the .xjava files. The source files written in the Java programming language and generated by the mapping tool. Queries can use methods or fields that are defined in the .xjava files. For example, you can write a query that enables an application program to retrieve customers using a customer identifier. For the query to work, you must have a customer identifier member in the Customer class, or a get method, such as getCustID, that uses the identifier to select the customer.

## OQL Queries

The following sections introduce some of the important features of the OQL syntax. For an overview of the complete OQL syntax, see The Object Database Standard: ODMG 2.0.

Recall that you place each OQL query in a separate .oql file. You must also import the classes that the query expects to access.

### Importing the Required Classes

You must import the persistent classes that your OQL query intends to access. A query can access any number of persistent classes. You must specifically import each class from which the query will be extracting data. Place the import statement at the top of the OQL source code file, just as you would import a package or class in a Java programming language source code file.

For example, suppose you construct a query that looks at parts ordered by a certain customer. This query needs information from three different classesa Customer class, an Orders class, and a Part class. At the top of the query file, prior to the select statement, you would include three import statements, in any order, as follows:

    import mydemo.Customer;

    import mydemo.Orders;

    import mydemo.Part;

    SELECT c

    FROM Customer c, Orders o, Parts p

    WHERE c.Id = 1011

    ...;

### select Statements

The most common type of OQL query consists of a select statement. A select statement, in turn, consists of three clauses: SELECT, FROM, and WHERE. The WHERE clause is optional. Although this document shows OQL reserved words in uppercase, OQL statements and clauses can be in either all uppercase or all lowercase (but not mixed case).

Typically, you construct a select statement to select and return objects or values from a specified class or classes. Normally, a select statement returns a collection or BAG unless directed otherwise. A BAG object is an unordered collection of elements that might contain duplicates. A select statement can also return a SET.ASET object is an unordered collection of elements with no duplicates. In addition, a select statement can return a LIST.ALIST object is an ordered collection of elements.

You can limit the result of the selection with the WHERE clause, which lets you apply a Boolean test to the selection. Thus, the simple form of a select statement is:

    SELECT

A select statement always begins with the SELECT clause. The SELECT clause specifies the objects or values that are returned from the classes or collections identified by the FROM clause. These objects can be entire objects, such as customers, or they can be attributes or fields within an object, such as customer names. The SELECT clause specifies which object, or value held by the object, that the query will extract and return to the application program. When the SELECT clause specifies parts of objects, it should list the parts or elements that it wants to select. It might also show how to extract the value. For example, the following SELECT clause directs the query to return only the customer name element in the customer object, rather than the entire customer object. It extracts the name using the getName method, which the Customer class defines.

The Java Blend tool requires parentheses after all method names, while the ODMG standard does not require parentheses for methods that have no parameters.

    SELECT c.getName()

    FROM Customer c;

Rather than using the method getName, the query would have better performance if it used the member field name:

    SELECT c.name

    FROM Customer c;

If you select objects, you can do further manipulation on these objects. The query:

    SELECT c FROM Customer c;

selects the Customer objects and enables you to do further processing on them in your application. For example, you might print or update the customer addresses.

Even though you select the entire object, you will not see changes made to this same object by separate transactions.

If you select portions of an object, that is, fields, you get a copy of the objects fields. The Java Blend tool, when it returns portions of objects, returns these values as literals. Because they are literals, their values do not change.

### Using Fields or Method Calls

You can write your query to use either a field, even though fields are marked as private, or a method call. From a performance standpoint, it is always preferable to use an objects field in a query. Method calls are always more expensive from a performance standpoint, and you should avoid them when possible.

The Java Blend tool processes a query by evaluating portions of the query in the database, thus preselecting potential results. However, because a method call cannot be evaluated in the database, the Java Blend tool, before processing the actual method call, constructs an object for each row in a table and then evaluates the method in memory. Thus, a performance impact results if a query calls a method defined in the underlying class. Queries that do not include method calls do their processing in the database, and this is more efficient. For example, you might want to query a Customer class for those customers whose credit is on hold and who have orders greater than a certain amount.

You could write your query as follows:

    SELECT c FROM Customer c

    WHERE c.getCredit() = hold AND c.getOrderAmount() > 100000;

This would require that the Java Blend tool read the entire Customer table, create an object for each customer row, then evaluate the methods. As a result the query would be inefficient from a performance standpoint.

You could write the same query as:

    SELECT c FROM Customer c

    WHERE c.credit = hold AND c.getOrderAmount() > 100000;

When written this way, the database returns only those customers whose credit is on hold. Then, the runtime code checks each of these customers to see if it passes the order amount test, if c.getOrderAmount() > 100000. The Java Blend tool does some processing in the database, but it also does some in memory.

If the order amount is stored as a field rather than calculated using business logic, the query can run even faster when written to use the fields credit and orderAmount in the select statement, as follows:

    SELECT c FROM Customer c

    WHERE c.credit = hold AND c.orderAmount > 100000;

When written this way, the Java Blend tool processes the entire query in the database. Your query runs more efficiently and performance is much better.

### Returning Unique Values

Recall that a select statement normally returns a collection of elements that might have duplicates. (In this regard, the OQL SELECT statement functions just like the SQL SELECT statement.) You can also specify that the query return a set of unique elements with no duplicate values.

Use the DISTINCT keyword to return a set with no duplicates. SELECT DISTINCT works the same in OQL as it does in SQL. You use DISTINCT once in the select statement. DISTINCT must immediately follow the SELECT keyword in the clause.

The following query returns a set of unique department names, even if the database held many employees who worked for the same department:

    SELECT DISTINCT e.deptname FROM Employee e;

### Handling Null Values

In addition to the ODMG keyword NIL, the Java Blend tools OQL compiler also accepts the keyword NULL. These keywords are equivalent; NIL or NULL denotes a nonexistent object.

When you access a property of a NULL or NIL object, the result is UNDEFINED. OQL provides two operations to test on UNDEFINED: IS_UNDEFINED applied to UNDEFINED returns TRUE while IS_DEFINED returns FALSE. If the Boolean expression defined by the WHERE clause of a SELECT-FROM-WHERE returns UNDEFINED, this is handled as if the expression returns FALSE. UNDEFINED is a valid element of collection. Any other operation with an UNDEFINED operand returns UNDEFINED. The value UNDEFINED is represented as NULL in the Java programming environment.

To illustrate, suppose you have a Customer class that refers to sales region. You have three customers, one in the east region, another in the west region, and a third with a NULL region.

The SELECT statement:

    SELECT c FROM Customer c WHERE c.sales_region.name = "East";

returns the customer in the east sales region. For the customer with no region, the Boolean expression c.sales_region.name is evaluated to UNDEFINED, which means this customer is not included in the result of the query.

The statement:

    SELECT c FROM Customer c WHERE IS_DEFINED(c.sales_region.name);

returns two customers, one from the east region and the other from the west sales region. If the same SELECT statement used the IS_UNDEFINED operation, it would return the one customer not yet assigned to a sales region.

Lastly, the statement:

    SELECT c.sales_region FROM Customer c;

returns sales regions East, West, and NULL.

### Constructing STRUCT Statements

The Java Blend OQL compiler lets you construct a STRUCT to hold query results. Use the STRUCT keyword immediately following the SELECT keyword. When you construct a STRUCT, you are actually associating an attribute name of your choice with a column value.

For example, you might want a query that selects customer name, sales region, and the number of orders placed by that customer and have that information returned as a STRUCT. Your STRUCT declares the attributes name, sales_region, and order_number, from which it extracts values from the customer record, as follows:

    SELECT STRUCT (name : c.custName,

    sales_region : c.sales_region,

    order_number : COUNT (c.ordersForCustomer) )

    FROM Customer c;

Note that the COUNT keyword extracts an integer value. In this example, it extracts the number of orders for a customer. Refer to Handling struct Results on page 123 for information on how to handle struct results in your application.

### FROM Clauses

The FROM clause directs the query to the set of objects from which the data extraction should be made. It essentially consists of a variable declaration and the class or collection of objects (or parts of objects) that should be searched for the results. At least one IN or AS construct follows the keyword FROM. The IN or AS construct specifies collectionstypically, a set of classes and subclasses whose objects can be selected.

There are three equivalent forms:

    SELECT c FROM c IN Customer;

    SELECT c FROM Customer c;

    SELECT c FROM Customer AS c;

Each select statement indicates that all Customer objects should be returned.

Because select expressions also return collections, you can nest select statements within the FROM clause. For example, suppose you want to query Employee objects, but you want to see only employees whose age is less than 40. You can nest a select statement within the FROM clause so that it selects only employees whose ages are less than 40, as follows:

    SELECT e

    FROM (SELECT x

    FROM Employee x

    WHERE x.getAge() < 40) e;

The statement above, of course, is equivalent to the simpler query:

    SELECT e

    FROM Employee e

    WHERE e.getAge() < 40;

In addition, the same query would run more efficiently if you used the variable age instead of the method getAge. This is the preferred form of the query, and it looks as follows:

    SELECT e

    FROM Employee e

    WHERE e.age < 40;

### Navigating Collections

You can use the FROM clause to navigate related objects or sets of collections. Designate the collections from which the query should process within the FROM clause. This designation differs from traditional SQL, which depends on an additional WHERE clause.

For example, suppose you have a set of customers. Each customer has a set of orders, and each order might include any number of parts. In OQL, you could query the parts ordered by a particular customer as follows, using the Customer field name to identify the customer:

    SELECT p

    FROM Customer c, c.orders o, o.part p

    WHERE c.name = ABC Company;

In this example, you know from examining the three classesCustomer, Orders, and Partthat relationships exist among the three classes. Each class includes members that reference one or more of the other classes. For example, the Customer class includes a collection member for orders, while the Orders class has both a collection member for parts and an object reference for customer. Your OQL query uses these members for its navigation. That is, the query uses the orders member to reach the orders for a customer; then it uses the part member to reach the parts on a particular order. However, notice that you need not specify these relationships when you write the query.

To do the equivalent in SQL, you must specify all the relationships between the tables. This results in a more complex statement and requires much more knowledge of the organization of the database tables. For example, you must know how the different tables are related to each other and the key column names. The equivalent SQL statement might be:

    SELECT p.partno, p.partname, p.partquantity ...

    FROM Customer c, Orders o, Part p

    WHERE c.name = ABC Company AND

    o.cid = c.cid AND

    p.partno = o.partno;

Notice also that you can select an entire object in the OQL statement and then reference individual fields or members from that object in the FROM and WHERE clauses.

### Joins

Use the FROM clause within a select statement to perform join operations on your data. The FROM clause lets you join collections that are either directly related or not. For example, you can accomplish a join as follows:

    SELECT DISTINCT s.company

    FROM Manager m,

    m.shares s;

This query returns a set of all company names in which managers own shares of stock. The keyword DISTINCT is added to eliminate duplicates in the returned set.

### WHERE Clauses

The optional WHERE clause always contains a Boolean expression to restrict the selection. Within the WHERE clause you can use Boolean operators (NOT, AND, OR), comparison operators (<, >, =, !=, <=, and >=), and a membership operator (IN). The comparison operators for equality (=) and inequality (!=) can be used with all data types; the other comparison operators can be used only with data types that can be sorted. See Sorting Query Results on page 97 for a list of those data types.

Always enclose string literals used in Java Blend queries in double () quotes. This differs from the ODMG standard, which allows you to use either single quotes or double quotes to enclose string literals. However, you enclose character variables in single () quotes in OQL.

You also have the capability of doing simple pattern matching, comparing one value to another using the LIKE operator. Two wildcards are available for pattern matching: the percent (%) sign and the underscore (_) . The underscore matches a single character. The percent sign matches an arbitrary number (including zero) of characters. It functions as a truncation operator if placed at both ends of a string pattern.

For example, the following select statement returns a set of customer objects whose addresses contain the substring CALIF:

    SELECT c

    FROM Customer c

    WHERE c.address LIKE %CALIF%;

The Java Blend tool does not recognize the question mark (?) or the asterisk (*) as a wildcard.

You can specify a range in the WHERE clause, using the BETWEEN and AND keywords, as follows:

    expression BETWEEN value AND value

Specifying a range selects objects that fall within the bottom and top limits of the range. For example, the following WHERE clause selects employees whose salaries are between $50,000 and $90,000 inclusive:

    WHERE e.salary BETWEEN 50000 AND 90000

This clause is equivalent to:

    WHERE 50000 <= e.salary AND e.salary <= 90000

The BETWEEN expression is not part of the ODMG OQL definition.

### Sorting Query Results

You can apply some additional clauses to the select statement. These clauses instruct the select statement to return the extracted items in sorted order or grouped by a particular value.

Place query results in sorted order using the ORDER BY keyword. This keyword always returns a LIST. You can sort data in ascending order, by specifying ASC, or in descending order, with the keyword DESC. If you do not specify whether the order should be ascending or descending, OQL uses the previous ordering specification within the ORDER BY clause. If no previous ordering specification is given, the default is ascending. For example, the following statement:

    SELECT e FROM Employees e

    ORDER BY e.salary DESC, e.name ASC, e.departno;

returns employees sorted in descending order by salary, then in ascending order by name and department number. Keep in mind that ordering of elements must be done on values whose types allow order-based comparison. Ordering can be done on the following types:

- Primitive types: byte, short, int, long, float, double, char
- Wrapper classes: Byte, Short, Integer, Long, Float, Double, Character
- Math types: BigDecimal, BigInteger
- String
- Date (com.sun.javablend.types.Date)
- Time (com.sun.javablend.types.Time)
- Timestamp (com.sun.javablend.types.Timestamp)

Note: String is a keyword in OQL. Use string or STRING to specify a String type.

### Date Literals

OQL provides literals for:

- Date. The keyword DATE followed by a single quoted string of the form year-month-day (DATE '1997-11-07')
- Time. The keyword TIME followed by a single quoted string of the form hour:minutes:seconds (TIME '14:23:05.3')
- Timestamp. The keyword TIMESTAMP followed by a single quoted string consisting of a date and a time (TIMESTAMP '1997-11-07 14:23:05.3')

The Java Blend tool uses the class java.text.SimpleDateFormat when parsing dates, times, and timestamps. (The Java Blend tool, however, further processes the output of java.text.SimpleDateFormat.)

Table 5-1 shows the format of the time pattern strings that the Java Blend tool passes to the class java.text.SimpleDateFormat. Refer to the Java 2 Platform documentation for a description of that class and of codes used in Table 5-1.

When processing time pattern strings, the Java Blend tool uses lenient parsing; calls to the method SimpleDateFormat.isLenient would return TRUE.

Table 5-1 Time Pattern Strings Passed by the Java Blend Tool

Data type | Time Pattern String
Date | yyyy-MM-dd
Time | HH:mm:ss[.SSS]
Timestamp | yyyy-MM-dd HH:mm:ss[.SSS

### Passing Parameters to Queries

Your query can accept parameters from a Java application. The Java application that is to use your query sets a value for the parameter variable. When it runs the query, it passes the parameter to the query. The query in turn uses the value from the application for its matching value in the query.

A parameterized query is one that, once coded, cannot change dynamically in the programonly the values of the parameters that are passed in to the query can change. A parameterized query requires a set of predefined parameters (or no parameters).

Suppose you want to have an application select customers by customer identification numbers so it can retrieve one customer at a time. To accomplish this, you write a query that accepts an identification number as a parameter. Later, the application simply instantiates this query object, and when it runs the query, it passes the value for which the query is to search.

To use a parameter in a query, you just specify the parameterpreceded by the dollar ($) sign and the type of the parameter within parentheses. In this example, the customer identification number is an integer value. You specify the name of the parameter, id, preceded by the dollar sign and the parameter type, int, within parentheses: $(int)id. Specifying the parameter type makes it possible to check the type at query development and query runtime.

To illustrate, you could start with the generated query to get all customers, OQLGetAllCustomer.oql. Then modify that query to create another query to get only the customer whose customer identification number matched a specified value. To achieve this objective, add the following WHERE clause to the select statement, as shown:

    import mydemo.Customer;

    SELECT o FROM Customer o WHERE o.custId = $(int)id;

The Customer class declares the custId field. The syntax $(int)id indicates that id is a parameter of type int.

You can also pass a collection to a query as a parameter in the FROM clause. To do this, you use the $(DCollection) syntax in front of the parameter name for the collection. For example, you might write a query to select from a collection of customers whose sales region is the East coast. The parameter name in this example is called CustomersReg:

    import com.sun.javablend.DCollection;

    import mydemo.Customer;

    SELECT c FROM $(DCollection)CustomersReg c

    WHERE ((Customer)c).SalesRegion = East Coast;

You must cast the contents of the collection to the proper type. In this example, the result c is cast to Customer type. You can also use parameters in the SELECT clause. In general, though, this is useful only with nested queries.

## Advanced OQL

This section describes advanced OQL.

### Subqueries

A subquery is a SELECT statement that nests within another SELECT statement. The SELECT statement always returns a collection. You can use the SELECT statement any place that a collection can be used. Thus, a SELECT clause might contain another SELECT statement that describes how to extract a value or further refines the first SELECT criteria. Be sure to include parentheses around nested SELECT statements. The OQL compiler reports a syntax error if these parentheses are missing.

When a statement includes a subquery, the subquery evaluates first. The outer SELECT statement then acts on the results of the inner statement. If the statement contains many nested SELECT statements, OQL evaluates the innermost statement first, then the statement that immediately encloses the innermost one, and so on.

For example, you could expand the statement:

    SELECT c.name FROM Customer c;

to nest another SELECT statement that checks for customers whose orders are greater than a certain quantity. The nested SELECT statement, instead of allowing the first SELECT clause to return all customer names, limits the selection to only those customers with orders greater than the specified quantity. Whereas the previous query returned a collection of names, this query returns a collection of constructed STRUCT statements. There is one STRUCT for each customer name. It includes the name of the customer and then a collection of the quantities for each order for that customer.

For example, the statement:

    SELECT STRUCT (name : c.name,

    order_amt : (SELECT o.qty

    FROM o in c.getOrders()

    WHERE o.qty > 100))

    FROM Customer c;

returns a collection of STRUCT statementsone STRUCT per customer. Each STRUCT contains a collection of order quantities for that customer, as follows:

    <John, <102, 155, 200>>

    <Kenneth, <300>>

    <Mary, <1110, 410>>

You could write the query to return the same data, but grouped differently. You could write it as follows:

    SELECT STRUCT (name : c.name,

    order_amt : o.qty)

    FROM Customer c, c.getOrders o

    WHERE o.qty > 100;

Written this way, the query returns a collection of STRUCTs where each STRUCT includes the customer name and one order quantity. If a particular customer has more than one qualifying order, another STRUCT is in place for that customer for each order quantity. Thus, the query might return the following:

    <John, 102>

    <John, 155>

    <John, 200>

    <Kenneth, 300>

    <Mary, 1110>

    <Mary, 410>

A nested SELECT statement, such as the previous example, accomplishes a join operation. More typically, you use the FROM clause for joins.

### Aggregates

OQL supports the following aggregate operators: COUNT, MAX, MIN, AVG and SUM.

The COUNT keyword returns the frequency with which an element occurs within a collection. The COUNT keyword extracts an integer that represents the number of occurrences of an element. For example, the following query determines the number of times a particular customer occurs in a customer table:

    COUNT(SELECT c

    FROM Customers c

    WHERE c.custName = "Watson")

The MAX keyword calculates the maximum value in a collection. For example, the following query determines the maximum salary of all employees:

    MAX(SELECT e.salary FROM Employees e)

Replacing the keyword MAX by MIN the query would calculate the minimum salary. The keyword AVG returns the average salary. The keyword SUM calculates the sum of all salaries.

### Grouping

A group query allows you to split a collection into partitions. Use the keyword GROUP BY, followed by a list of one or more expressions, called partition attributes. Each element of the collection belongs to the same partition if it matches the values of the partition attributes. The following example query returns the employees grouped by department number:

    SELECT partition FROM Employee e GROUP BY department : e.deptno

A GROUP BY query builds the cartesian product by the FROM clause, and filters its result by retaining only those elements that satisfy the WHERE clause. It then evaluates the partition attributes for all elements of the Cartesian product. All elements that match the same partition attribute values belong to the same partition. The result of this operation is a set of struct statements, where each struct consists of the partition attributes followed by the bag of elements that match this particular valued partition. This struct field is conventionally called partition. Finally the SELECT clause is applied to this partitioned set. In the example above, the result after the group operation is of type:

    set<struct(department : int, partition : bag<struct(e: Employee)>)>.

The SELECT clause extracts the partition field, so the query returns a set of

    struct(e:Employee) values.

You can also add a HAVING clause to the query in order to filter specific partitions that match the predicate specified after the keyword HAVING. The following query returns the department number together with the average salary of all employees of a partition, if the average salary is more than 30000:

    SELECT department, AVG(SELECT x.e.salary FROM partition x)

    FROM Employee e

    GROUP BY department : e.deptno

    HAVING AVG(SELECT x.e.salary FROM partition x) > 30000

### Query Results

Depending on how you construct the SELECT statement, the query returns either a collection of elements or a single element. While a collection of elements might contain a single element, your application handles collection results differently than a single element.

The outermost operation of the query determines whether the result is a collection or a single element. If the outermost operation is a SELECT-FROM-WHERE statement, it always returns (you always get back) a collection. The form of the query determines the type of the collectionthat is, whether it is a list, a set, or a bag. If the query includes an ORDER BY clause, you get back a LIST. If the query includes a DISTINCT clause (without an ORDER BY clause), then you get back a SET. Otherwise, you get back a BAG.

While you might construct the SELECT-FROM-WHERE statement to return only a single element, you still get back a collectionin this case, a collection of one element. For example, the following statement returns a collection of one customer element because the id parameter happens to be unique over all customer records:

    SELECT c FROM Customer c

    WHERE c.id = 10031;

Queries that return a single element have an operation other than a SELECT-FROM- WHERE statement as the outermost operation. These queries might have the keyword COUNT or ELEMENT as the outermost operation.

The COUNT keyword, when it appears as the outermost operation, evaluates an expression and returns a single int value. For example, the following query returns an integer that represents the number of persistent objects of class Person:

    COUNT(SELECT p FROM Person p);

## OQL Syntax and Grammar

This section lists the complete syntactical definition for OQL in Extended Backus Naur Form (EBNF).

### Syntax Conventions

In EBNF, each rule has the form:

    symbol : expression

The syntax expression expression describes a set of phrases named by the non-terminal symbol symbol. The following symbols are used for the syntax expressions:

- n A non-terminal symbol that has to appear at some place within the grammar on the left side of a rule. All non-terminal symbols have to be derived to terminal symbols.
- T Represents the terminal symbol t.
- x y Represents x followed by y.
- x |y or (x |y) These two forms represent x or y.
- [x] Represents x or empty.
- {x} Represents a possibly empty sequence of x.

### OQL Grammar

    root : {importClause} query [;]

    importClause : IMPORT qualifiedName [AS identifier] ;

    qualifiedName : identifier{. identifier}

    query : selectExpr

    	| expr

    selectExpr : SELECT [DISTINCT] projectionAttributes fromClause

    	[whereClause] [groupClause] [orderClause]

    projectionAttributes : projection{, projection} | *

    projection : field

    	| expr [AS identifier]

    fromClause : FROM iteratorDef {, iteratorDef}

    iteratorDef : expr [AS] identifier

    	| identifier IN expr

    whereClause : WHERE expr

    groupClause : GROUP BY partitionAttributes {havingClause}

    partitionAttributes : projection {, projection}

    havingClause : HAVING expr

    orderClause : ORDER BY sortCriteria

    sortCriteria : sortCriterion {, sortCriterion}

    sortCriterion : expr [ordering]

    ordering : ASC | DESC

    expr : orExpr

    orExpr : andExpr {OR andExpr}

    andExpr : quantifierExpr {AND quantifierExpr}

    quantifierExpr : FOR ALL inClause : rangeExpr

    	| EXISTS inClause : rangeExpr

    inClause : identifier IN expr

    rangeExpr : equalityExpr {BETWEEN equalityExpr AND equalityExpr}

    equalityExpr : relationalExpr {equalityOp [compositePredicate] relationalExpr}

    	| relationalExpr {LIKE relationalExpr}

    equalityOp : =

    	| !=

    relationalExpr : additiveExpr {relationalOp [compositePredicate] additiveExpr}

    relationalOp : <

    	| <=

    	| >

    	| >=

    compositePredicate : SOME

    	| ANY

    	| ALL

    additiveExpr : multiplicativeExpr {+ multiplicativeExpr}

    	| multiplicativeExpr {- multiplicativeExpr}

    	| multiplicativeExpr {UNION multiplicativeExpr}

    	| multiplicativeExpr {EXCEPT multiplicativeExpr}

    	| multiplicativeExpr {|| multiplicativeExpr}

    multiplicativeExpr : castExpr {* castExpr}

    	| castExpr {/ castExpr}

    	| castExpr {(% | MOD) castExpr}

    	| castExpr {INTERSECT castExpr}

    castExpr : ( type ) castExpr

    	| inExpr

    inExpr : unaryExpr {IN unaryExpr}

    unaryExpr : + unaryExpr

    	| - unaryExpr

    	| ABS unaryExpr

    	| NOT unaryExpr

    	| postfixExpr

    postfixExpr : primaryExpr {[ index ]}

    	| primaryExpr {(. | ->) identifier [argList]}

    index : expr {, expr}

    	| expr : expr

    argList : ( [valueList] )

    primaryExpr : conversionExpr

    	| collectionExpr

    	|  aggregateExpr

    	|  undefinedExpr

    	|  collectionConstruction

    	|  structConstruction

    	|  identifier [argList]

    	|  queryParam

    	|  literal

    	|  ( query )

    conversionExpr : LISTTOSET ( query )

    	|  PICK ( query )

    	|  ELEMENT ( query )

    	|  DISTINCT ( query )

    	|  FLATTEN ( query )

    collectionExpr : FIRST ( query )

    	|  LAST ( query )

    	|  HEAD ( query )

    	|  TAIL ( query )

    	|  UNIQUE ( query )

    	|  EXISTS ( query )

    	|  OCCURRENCE ( expr , expr )

    aggregateExpr : SUM ( query )

    	|  MIN ( query )

    	|  MAX ( query )

    	|  AVG ( query )

    	|  COUNT ( (query 	|  *) )

    undefinedExpr : IS_UNDEFINED ( query )

    	|  IS_DEFINED ( query )

    structConstruction : STRUCT ( fieldList )

    fieldList : field {, field}

    field : identifier : expr

    collectionConstruction : ARRAY ( [valueList] )

    	|  SET ( [valueList] )

    	|  BAG ( [valueList] )

    	|  LIST ( [valueList] )

    valueList : expr {, expr}

    queryParam : $ [( type )] (identifier 	|  integer-literal)

    type : qualifiedName

    	|  BOOLEAN

    	|  BYTE

    	|  SHORT

    	|  INT

    	|  LONG

    	|  FLOAT

    	|  DOUBLE

    	|  CHAR

    	|  STRING

    identifier : letter {letter|  digit |  _ }

    literal : integer-literal

    	|  long-literal

    	|  float-literal

    	|  double-literal

    	|  char-literal

    	|  string-literal

    	|  boolean-literal

    	|  object-literal

    	|  undefined-literal

    	|  date-literal

    	|  time-literal

    	|  timestamp-literal

    integer-literal : digit {digit}

    long-literal : digit {digit} (l |  L)

    float-literal : digit {digit} . digit {digit}

    [(E | e) [+ | -]digit {digit}] (f |  F)

    double-literal : digit {digit} . digit {digit}

    [(E | e) [+ | -]digit {digit}] [d |  D]

    char-literal : 'character'

    string-literal : "{character}"

    boolean-literal : TRUE |  FALSE

    object-literal : NIL |  NULL

    undefined-literal : UNDEFINED

    date-literal : DATE ' integer-literal - integer-literal -integer-literal '

    time-literal : TIME ' integer-literal : integer-literal :double-literal '

    timestamp-literal : TIMESTAMP ' integer-literal - integer-literal -integer-literal integer-literal : integer-literal: double-literal'

    character : letter

    	|  digit

    	|  special-character

    letter : A | B |  ... |  Z |

    a | b |  ... |  z

    digit : 0 |  1 |  ... |  9