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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…