plsql - Nested Subquery in Oracle Apex 20.1 chart

Question

for my chart in Oracle Apex 20.1 i want to implement a nested subquery where data shown depends on someones permission in a team.

I have on table (ASSESS_TEAM) with team and the columns:

• id
• department
• date
• result
• creator
• name

Furthermore, I have a second table (PROJECT_TEAM) where one can create a new team and add owners and members and rights with the following columns:

• name
• creator
• date
• owner
• member
• owner_rights
• member_rights

For my chart I need to create secure view where only the creator, the team owner and the members can view the assessed team with the results. So I need to check if the logged user is in a project team or not. If so, he can see the chart and vice versa.

My current approach is to use a nested subquery. But although the code seems valid nothing every record is shown in the chart without a filter

The code:

SELECT  date, result, department, name from assess_team

where exists

(SELECT creator, owner, member from project_team

WHERE 

name = :PX_ID

AND

(creator= :APP_USER OR owner= :APP_USER OR member= :APP_USER ));

I also tried to join the two table, but then i get multiple entries in my chart, since there can be multiple owners and members in a team.

Any idea on how to solve this. Any help is appreciated. Thank you.

Answer 1

You can use corrects structure

SELECT  date, result, department, name 
from assess_team at
where exists 
(SELECT * from project_team
WHERE name = at.name
AND name = :PX_ID
AND (creator= :APP_USER OR owner= :APP_USER OR member= :APP_USER ));

More details : https://www.techonthenet.com/oracle/exists.php