I have a table called pims_rpc_details (attached) which includes a key column rpc_stcok_number and 8 columns connection1-8. I need to ensure that every entry in the array connection1-8 (across rows and columns) remains unique before submitting an update in an APEX app. I have the following code that I know works in SQL developer:
WITH unpivoted_data AS
(
select u.*
, case
when count(*) over (partition by val) > 1
then 'Duplicate'
else null
end as chk
from pims_rpc_details
unpivot exclude nulls
(
val for ccol in (connection1, connection2, connection3, connection4, connection5, connection6, connection7, connection8)
) u
)
select rpc_stock_number, ccol, val
from unpivoted_data
where chk = 'Duplicate'
order by val;
When I enter the above code as 'validation expression 1' of a validation type 'NOT Exists' (validated when no rows produced) , and submit an update, I get this error
Error processing validation.
ORA-00907: missing right parenthesis
Can you please tell me why this is and how to fix it?
pims_rpc_details.7z (7.18 KB)Also I used a simple test SQL query to generate a row, to fail validation, and that worked, but I noticed that the generated row was not displayed. Can a pop or other method be created to display the rows of the query?
I'm using APEX 5.1.4 on RedHat Linux rel 4 (yes, sorry, it's very old) with an Oracle 12.1 DB
Thanks, Chris