Hi, I'm new to using Apex and I'm trying to tie checkbox items into the sql statement that I'm using to pull my data.
the SQL that builds the array of checkboxes is as follows:
select firstname || ' ' || lastname as display_value, id as return_value from staff
My label for the checkbox item is P3_NEW.
I discovered in my research that the array of values are delimited by colons. So in the post-calculation computation, I put '(' || REPLACE(:P3_NEW,':',',') || ')'. That gives me (60006,12456,88534).
The SQL in my main report ends in the following:
AND mystaff.id in : P3_NEW
so, in my estimation, it should produce SQL equal to "And MYSTAFF.ID in (60006,12456,88534)"
This code ends up giving me an ORA-01722: invalid number error.
I came across Oracle APEX 5.0 - SQL query based on (multiple) checkbox values - Stack Overflow
and tried integrating that into my code in the form of:
(instr(':'||:P3_NEW||':')
or :P3_NEW is null
)
)
and while that did take care of the invalid number error, my statement no longer pulls results.
Any help would be appreciated.