11.1 Subqueries defined
A subquery is a more flexible kind of query filter that allows you to restrict values in more sophisticated ways than is possible with a ordinary query filters.
Subqueries are more powerful than ordinary query filters for the following reasons:
• They allow you to compare the values of the object whose values are used to restrict the query with values from other objects.
• They allow you to restrict the values returned by the subquery with a WHERE clause.
• They allow you to pose complex questions that are difficult or impossible to formulate with simple query filters. For example: what is the list of customers and their associated revenue where the customer purchased a service that had previously been reserved (by any customer) in Q1 of 2003?
Subqueries work by modifying the SQL that is generated to retrieve the query data. The SQL contains a subquery that restricts the data returned by an outer query. SQL is the query language supported by all relational databases (RDBMS), although each database has its own syntax. For more information on SQL subqueries, see any book on SQL.
Note:
• Subqueries are not supported in all databases. If they are not supported, the option to build subqueries does not appear in the query panel.
• You can build subqueries using dimensions, attributes and measures only. You cannot build subqueries using hierarchical objects.
Related Topics
•To build a subquery
•Subquery parameters
11.2 To build a subquery
1. Add the objects that you want to appear in the query to the Result Objects pane.
2. Select the object in the Result Objects pane that you want to filter with a subquery and click Add a subquery at the top right of the Query Filters pane.
The subquery outline appears in the Query Filters pane. By default the object you selected appears as the Filter object and Filter By object.
3. To add a WHERE condition to the subquery, drag a report object to the area of the subquery below the Drop an object here boxes.
4. To add a WHERE condition to the subquery, drag a report object to the area of the subquery below the Drop an object here boxes.
You can use an existing subquery or standard query filter as a WHERE condition in a subquery. To do so, drag and drop the existing filter or subquery to the area of the subquery below the Drop an object here boxes. To copy rather than move the existing filter to the WHERE condition, hold down the Control key while dragging and dropping. In this case the existing filter remains in its initial place and becomes part of the WHERE condition of the subquery.
5. Select the operator and values used to filter the object in the WHERE condition.
6. Click Subquery to add an additional subquery to the query filter.
In addition to linking subqueries in AND or OR relationships, you can nest them (create subqueries within subqueries) by dragging an existing subquery to the area beneath the Drop an object here boxes. In this case the inner subquery becomes part of the WHERE condition of the outer subquery.
To copy rather than move the subquery to the WHERE condition, hold down the Control key while dragging and dropping. In this case the second subquery remains at the same level as the first, and becomes part of the WHERE clause of the first.
By default the two subqueries are linked in an AND relationship. Click the AND operator to toggle between AND and OR.
7. To nest a subquery (create a subquery within a subquery), drag an existing subquery to the area beneath the Drop an object here boxes.
To copy rather than move the subquery to the WHERE condition, hold down the Control key while dragging and dropping. In this case the second subquery remains at the same level as the first, and becomes part of the WHERE clause of the first
The inner subquery becomes part of the WHERE condition of the outer subquery.
Related Topics
•Subqueries defined
•To find out which customers bought a service that had previously been reserved in Q1 of 2003, and how much revenue have they generated
•Subquery parameters
11.3 To find out which customers bought a service that had previously been reserved in Q1 of 2003, and how much revenue have they generated
The query panel is open.
1. Drag the Customer and Revenue objects to the Result Objects pane of the Query Panel.
2. Select the Service object.
3. Click Subquery.
The subquery outline appears in the Query Filters pane.
Note:
The selected object appears in both boxes in the subquery outline. You often use the same object in both boxes, although this is not required. If the objects do not return any common values, the subquery returns no values, and the query therefore returns no values.
4. Drag the Reservation Year object to the area of the subquery outline beneath the Service objects to add a WHERE condition on the Reservation Year object .
5. Set the Reservation Year condition operator to Equal To.
6. Type ‘FY2003’ in the Type a constant box.
7. Drag the Reservation Quarter object to the area of the subquery outline beneath the Service objects to add the Reservation Quarter object to the WHERE condition.
8. Set the Reservation Quarter condition operator to Equal To.
9. Type ‘Q1’ in the Type a constant box.
10. Click Run Query to run the query.
11.4 Subquery parameters
A subquery or set of subqueries contains the following parameters:
Description Parameter
The object whose values are used to filter the re-sult objects.
You can include more than one Filter Object. If you do, the values of the objects you select are concatenated.
Filter Object(s)
The object that determines which Filter Object values the subquery returns.
You can include more than one Filter By object.
If you do, the values of the objects you select are concatenated.
Filter By Object(s)
Description Parameter
The operator that specifies the relationship be-tween the Filter object and the Filter By object.
Because of database restrictions you cannot use certain combinations of operators and Filter By objects together. For example, if you use the Equal To operator with a Filter By object that re-turns multiple values, the database rejects the SQL because this type of subquery requires the Filter By object to return one value only.
In cases where the generated SQL is rejected by the database, you see an error message showing the error description returned by the database Operator
An additional condition that constrains the list of values of the Filter By object. You can use ordi-nary report objects, predefined conditions or ex-isting query filters (including subqueries) in the WHERE condition.
WHERE condition (optional)
If there is more than one subquery, this operator determines the relationship between the sub-queries.
AND - the conditions in all of the subqueries must be satisfied.
OR - the conditions in any one of the subqueries must be satisfied.
Relationship operator