41.5 Create Links Between REF CURSOR Queries

Currently, the queries that you have created are unrelated. To create relationships between them, you need to create group-to-group data links. The steps in this section will help you create the links.

To create links between REF CURSOR queries:

1. In the Data Model view, click the Data Link tool in the tool palette.

2. Click the title bar of G_DEPARTMENT_ID, and drag a link to the title bar of G_EMPLOYEE_ID.

3. Double-click q_container to display the PL/SQL Editor.

4. In the PL/SQL Editor, append code to the WHERE clause of the SELECT statement to specify which columns are being used as primary and foreign keys:

   After from job_history, add the following code:

   where :department_id = department_id;
   

   Be sure that the semicolon (;) now follows the WHERE clause.

   Note that :department_id is a bind variable referring to the DEPARTMENT_ID in G_DEPARTMENT_ID.

5. Click Compile.

6. If any compilation errors occur, check the code for syntax errors and recompile as needed.

7. Click Close.

8. In the Data Model view, click the Data Link tool in the tool palette.

9. Click the title bar of G_EMPLOYEE_ID and drag a link to the title bar of G_EMPLOYEE_ID1.

10. Double-click q_conclass to display the PL/SQL Editor.

11. In the PL/SQL Editor, add a WHERE clause to the SELECT statement:

    Insert your cursor between FROM EMPLOYEES and the semicolon (;), and press ENTER or RETURN to create a new line, then add the following code:

    where :employee_id = employee_id;
    

    Be sure that the semicolon (;) now follows the WHERE clause.

    Note that :employee_id is a bind variable referring to the EMPLOYEE_ID column in G_EMPLOYEE_ID.

12. Click Compile.

13. If any compilation errors occur, check the code for syntax errors and recompile as needed.

14. Click Close.

15. Your data model should look similar to the following:

    Figure 41-6 Data model with links

    
    Description of "Figure 41-6 Data model with links"
    
    

    Note:

    You can open the provided file ref_emp64.rdf and display the Data Model to compare your results.

16. Save the report as ref_64_your_initials.rdf.