Subqueries

A subquery can be used in quantified predicates, the EXISTS predicate, or the 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: Oct 22, 2010 12:21:29 AM CDT
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=compass&product=was-nd-zos&topic=cque_subquery
File name: cque_subquery.html