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:
In the Data Model view, click the Data Link tool in the tool palette.
Click the title bar of G_DEPARTMENT_ID, and drag a link to the title bar of G_EMPLOYEE_ID.
Double-click q_container to display the PL/SQL Editor.
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
.
Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
In the Data Model view, click the Data Link tool in the tool palette.
Click the title bar of G_EMPLOYEE_ID and drag a link to the title bar of G_EMPLOYEE_ID1.
Double-click q_conclass to display the PL/SQL Editor.
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
.
Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
Your data model should look similar to the following:
Note:
You can open the provided file ref_emp64.rdf
and display the Data Model to compare your results.
Save the report as ref_64_
your_initials
.rdf
.