6.4.2.2 Using Filtered Data Primary Keys

Use the SQL table() operator to use rows from a pipelined function in the data source query of any APEX region.

Using the SQL WITH clause as shown below, you can name the filtered region's list of primary keys returned by the region_pks() function. Then, your query can SELECT from the result_pks common table expression joined with the emp table on their respective empno column values. The join condition between left and right tables uses a common column name, so the shorthand using (empno) is easier to type than on emp.empno = result_pks.empno.

with result_pks as (
    select column_value as empno
      from table(region_pks('employees','EMPNO'))
)
select ename,
       sal
from result_pks
join emp e using (empno)

You can also involve the filtered results' primary keys in a WHERE clause when needed like this:

empno in (select column_value
            from table(region_pks('employees','EMPNO')))