This example exemplifies the use of O-SQL-AG aggregates in defining sub-queries. This query finds the name of the employee with salary greater than the average salary of his/her department. The following is the complex SQL query for this example.
Now we redefine average2 aggregate in SQL-AG, and then use PL/SQL to use the defined aggregate in sub-query. The following is the specification of the aggregate in SQL-AG.
The following PL/SQL code in file sub-query.sql uses average2 to solve the problem.
In the above code cursor c is used to retrieve records from the table emp. For each record in emp average salary for the department is computed. The partial results are in average2_package.gv_result.
Following is a call to the above PL/SQL code along with the output. (These commands were given at the sqlplus command line prompt)
