WebSphere Application Server - Express, Version 6.0.x     Operating Systems: AIX, HP-UX, Linux, Solaris, Windows

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

Concept topic    

Terms of Use | Feedback

Last updated: Jun 8, 2005 12:45:23 PM EDT
http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.express.doc/info/exp/ae/cque_subquery.html

© Copyright IBM Corporation 2002, 2005. All Rights Reserved.
This information center is powered by Eclipse technology. (http://www.eclipse.org)