Oracle Apex Select List LOV conditional query

Question

I have an Editable Interactive Grid (for product_sale table) with a Select List (to select a product) in Oracle Apex 20.2 app.

Currently I'm using below query to populate this list of values.

SELECT productName,productId FROM product WHERE productAvailability = 'Y'

There are a few products that I need to set the productAvailability as 'N'. When I made this change, Interactive Grid of product_sale shows productId instead of the productName.

What I need to achieve is, only show products with productAvailability = 'Y' for new records (when going to add new record in to the table by clicking Add Row button) and for the old records show the productName of the selected product regardless the productAvailability.

Table Structure

Sample Data

Interactive Grid View

How could I achieve this?

Answer 1

You can achieve this using the "cascading list of values" option in the column settings. Let me illustrate with an example on the EMP sample table - you should be able to translate this to your own code:

Situation: BLAKE can not be selected as manager for any other employees than the one he is already manager for.

This would be the select:

SELECT ename, empno FROM emp WHERE ename != 'BLAKE'

As expected, for records that have BLAKE as manager, the MGR column will display the id because the value is not in the result set of the select list query. This is the behaviour you are seeing.

The solution has 2 steps:

1. Union the query with a query that has the value of the current row. I have rewritten the query using a CTE.

WITH all_mgr(dv, rv) AS
(SELECT ename, empno FROM emp WHERE ename != 'BLAKE'
  UNION
 SELECT m.ename, m.empno 
   FROM emp e 
        JOIN emp m ON e.mgr = m.empno 
  WHERE e.ename = :ENAME
)
SELECT dv, rv FROM all_mgr

2. Make sure column ename from the current row is bind to :ENAME. In "Cascading List Of Values" set both "Parent Column" and "Items to Submit" to ENAME. Now the select list will take the current row value for :ENAME and the list of values will include 'BLAKE' for users that have 'BLAKE' as a manager.