In this example, the SQL query returns department and employee information from the DB2® database GSDB. The example creates pureQuery nested beans to organize employee information by department. The top-level bean is the Dept bean that contains a list of Emp beans.
The Dept1 class that is used with the example interface class contains data from the ORGANIZATION table.
The Dept2 class that is used with the example inline SQL query contains data from the ORGANIZATION table.
The Emp class retrieves data from the EMPLOYEE and EMPLOYEE_HISTORY tables. The Emp class defines the Emp bean that is the child bean for the Dept1 bean and Dept2 bean.
SELECT E.EMPLOYEE_CODE, E.FIRST_NAME, E.LAST_NAME, E.DATE_HIRED,
ORG.ORGANIZATION_CODE, ORG.ORGANIZATION_NAME_EN
FROM EMPLOYEE AS E, EMPLOYEE_HISTORY AS EH, ORGANIZATION AS ORG
WHERE EH.EMPLOYEE_CODE = E.EMPLOYEE_CODE
AND EH.ORGANIZATION_CODE = ORG.ORGANIZATION_CODE
AND EH.RECORD_END_DATE IS NULL
ORDER BY ORGANIZATION_CODE, E.LAST_NAME;
The EMPLOYEE_HISTORY
table can contain multiple records for an employee. The current employee
information does not have an end date. The predicate AND EH.RECORD_END_DATE
IS NULL restricts the query to return only the current employee
history. For information about the GSDB database and the tables that are used in this example, see Tables accessed by the pureQuery nested beans example.