Subqueries

A subquery can be used in quantified predicates, EXISTS predicate or IN predicate. A subquery should only specify a single element in the SELECT clause. When a path expression appears in a subquery, the identification variable of the path expression must be defined either in the subquery, in one of the containing subqueries, or in the outer query. A scalar subquery is a subquery that returns one value. A scalar subquery can be used in a basic predicate and in the SELECT clause of a dynamic query.

Example: Subqueries

SELECT  OBJECT(e) FROM  EmpBean e 
WHERE e.salary > ( SELECT  AVG(e1.salary) FROM  EmpBean e1)

The above query returns employees who earn more than average salary of all employees.

SELECT  OBJECT(e) FROM EmpBean e WHERE  e.salary > 
( SELECT AVG(e1.salary) FROM  IN  (e.dept.emps) e1  ) 

The above query returns employees who earn more than average salary of their department.

SELECT  OBJECT(e) FROM EmpBean e WHERE e.salary = 
( SELECT MAX(e1.salary) FROM IN (e.dept.emps) e1  )

The above query returns employees who earn the most in their department.

	SELECT OBJECT(e) FROM EmpBean e
WHERE e.salary > ( SELECT AVG(e.salary) FROM EmpBean e1 
WHERE YEAR(e1.hireDate) =  YEAR(e.hireDate)  )

The above query returns employees who earn more than the average of employees hired in same year.


Related concepts
EJB query language
Related tasks
Using EJB query



Searchable topic ID:   cque_subquery
Last updated: Jun 21, 2007 8:07:48 PM CDT    WebSphere Business Integration Server Foundation, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.wasee.doc/info/ee/ae/cque_subquery.html

Library | Support | Terms of Use | Feedback