Hi!
How to use select distinct with shuttle item in LOV section?
I use LOV with shuttle. example of code with demo tables in shuttle List of Values section:
select distinct t1.user_privilege d, t3.id r
from demo_privileges t1
left join privilege_roles t2 on t1.priv_id = t2.fk_priv_id — link table
left join demo_roles t3 on t2.fk_id = t3.id
This code return duplicate value for t1.user_privilege.
Thanks in advance!
Now check, I modified the app and let me know more details if not correct.
For example, when the user selects a value from LOV, then what should happen to the shuttle.
Thanks a lot Vinish, works!
The shuttle should be like now. I will try to add NVL function to the source of the shuttle, to display the correct value when the user opens a page. Because I disabled Display Null Value in the LOV item.
Was the problem only in set value DA?
Yes, I modified that query only.
In LOV item are roles and return id from demo_roles, for that I return id from demo roles in shuttle.
In shuttle item, I should be able to assign and delete privilege to a role.
For that, I need user_privilege in the shuttle. You can see this demo app there is multiple duplicate column in the shuttle. Workspace: apex1, user: testuser123, pass: pav12345
Your demo app is based on the ORDER tables. Please modify it as it is in your application with actual tables.
In my understanding, it should be like this only:
Create a LOV based on the Roles table and return the role id as you already doing it.
For the shuttle item, specify the query to get the privileges from the user_privileges table on behalf of the roles. Specify the LOV as a cascading item, and it should work.
Ok, I will try to fix the problem with your instructions, if I don’t succeed to fix the problem, I will create demo app with other tables. 🙂
Please, can you describe me this part “specify the query to get the privileges from the user_privileges table on behalf of the roles.” 🙂
Yes sure.
Your SQL query to get the privileges should be something like below:
You will get the distinct priv_id as the return value, which you can assign to the role id selected through the LOV.
Hi Vinish!
I failed to solve the problem 🙁
I created a new demo app
Workspace: apex1, user: testuser123, pass: pav12345
Correction: duplicate column value*
Hi Pavlos,
The distinct clause will not return the unique set of data because if t3.id is different for each row.
You are also joining the left outer join, which will return rows even if the records are not found in t2 and t3 tables. In this case, the null t3.id will return.
You are showing the user_previlege column from the demo_privileges table and the id from the demo_roles table. Against a privilege, there could be multiple roles, that is why it is returning the same privileges. I think you should show a role instead of user_privilege.