Diplaying a value from another column in the same table in Oracle APEX

user_15374811 0
Viewed 239

I am struggling with Oracle APEX. I have a Shuttle box with test runs. On top of that I have a filter that filters them based on Project. When I select 'All Projects' my test runs display correctly as shown below: enter image description here

HOWEVER When I cahnge the filter to a specific Project, the shuttle window on the right side will stop displaying names of the test runs and instead will show me the ID of the test run

enter image description here

My query for this configuration is:

select name as d, test_run_id as r From test_run 
Where project_jira_key=coalesce(:TR_BASED_ON_PROJECT, project_jira_key) 
and folder_id NOT IN(select folder_id 
from folder where name in (select column_value from table (apex_string.split(config.get('ATM_DASHBOARD_CONFIGURATION_FOLDER_EXCLUDE'), ','))));

I am still quite new to APEX and SQL in general but I assume that the "r" are the return values on the right side? I also assume that "test_run_id as r" should remain there as test_run_id is the primary key of that table. Is there a way to display test_run.name? It is a column in the same table. Thanks in advance

oracle-apex sql
1 Answers

The shuttle component shows the values that are in the page component on the right side and the rest of the available values on the right side based on the component source. Here is a small example to illustrate how it works on the emp/dept dataset.

  • Create a blank page with a static region and page item of P1_EMP of type "Shuttle" with source "SQL Query" and source:
SELECT ename, empno FROM emp

Run the page. Notice there is a list of employee names on the right side you can select. Right side is empty.

  • Create a before regions computation on P1_EMP of type static value and value "7566:7788" - these are the empno values for JONES and SCOTT, represented in a colon delimited list (how apex handles multi-select values)

Run the page. Notice that now JONES and SCOTT are selected. The left side shows all other values. What happens is that the apex engine checked the resultset (the employee numbers) of the component and rendered the display values (employee names) for both sides of the shuttle based on the source query

Now change the source query on P1_EMP to

SELECT ename, empno FROM emp WHERE ename IN ('KING','ADAMS')

Run the page. Notice that the right side nows values 7566 and 7788. There are no corresponding values in the source query result set for those values so id's are shown as their source value.

So there is the explanation why you see the numeric values on the right side of your shuttle. Those values are in the page item but they are not in the result set of your filtered query. Question is what behaviour do you want

  • do not display the values that are not in the query. Solution: set attribute "Display Extra Values" to "Off".
  • show the return values of the id's on the right side even if they do not appear in the source query. Solution: change the query so it includes the values that are already in the page item. This can be achieve by adding a union to the query that display all results without the filter. The UNION clause will filter out duplicate values if they exist:
SELECT ename, empno FROM emp WHERE ename IN ('KING','ADAMS')
UNION ALL
SELECT ename, empno FROM emp WHERE empno IN (select column_value from table (apex_string.split(:P54_EMP, ':')))
user_41898140
Related