I'm trying to create a validation on create and update of an interactive editable grid in Oracle Apex 5.1. I right click the effective_start_date column and then choose Create Validation. From here I then set Type to No Rows returned with the idea of doing a select where there are other rows for the same date range as the current row being created or updated.
I have the following query:
select *
from my_table c
where c.salary_band = :salary_band
and c.id != :id
and c.effective_start_date <= :effective_end_date
and c.effective_end_date >= :effective_start_date
However this does not work as expected (error when there are overlapping dates for a salary band). Even when I comment out the last two lines it does not error on a duplicated salary band value. My assumption is that I am not referring to the current row values of the interactive grid entered during the create or update incorrectly although no error is thrown when I create the validation. How do I refer to the current row values entered in the interactive grid or what am I doing wrong here?
