Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use Checkbox values in a Sql Select with In clause

PhilMan2Oct 22 2021

I'm using Apex 19.1 on an 18.c database.
In a Static Content region, I have a Checkbox page item defined with the following characteristics:

Identification / Name = P273_WEEKDAY_SELECTOR
Identification / Type = checkbox
List Of Values / Type = Static Values
List Of Values / Static Values = Mon, Tue, Wed, Thu, Fri
List Of Values / Display Extra Values = No

Another region on the same page has the following characteristics:

Identification / Name = Weekday Attendance
Identification / Type = Chart

The Series for that chart has a SQL Query for the Source:

Select
     ls.prim_key
   , ls.weekday
   , to_char(ls.event_date, 'MON-DD-YYYY') as "Event_Date"
   , NVL(ls.count_online, 0) as "Online"
   , 'Online' series
From 

     EVENT_STATS ls
Where
   ls.prim_key is not null And
   trunc(ls.event_date) >= :P273_start_date And
   trunc(ls.evemt_date) <= :P273_end_date And
--   ls.weekday in ('Mon', 'Tue')
   ls.weekday in ('''' || regexp_replace(:P273_weekday_selector, ':', ''', ''' ) || '''')
Order by   
   ls.event_date;

I'm having trouble accessing the values stored in the checkbox (P273_weekday_selector). I used a regular expression to convert the colons into (single-quote / comma / space) and started and ended the entire list in single quotes.
I created a Dynamic Action to update the chart whenever the checkbox was changed.

When / Event = Change
When / Selection Type = Item(s)
When / Item(s) = P273_WEEKDAY_SELECTOR

Then I created two actions:

True / Execute PL/SQL code
Settings / PL/SQL Code = null;
Items to Submit = P273_WEEKDAY_SELECTOR
Execution Options / Fire when event result is = True

and

True / Refresh
Affected Elements / Selection Type = Region
Affected Elements / Region = Weekday Attendance
Execution Options / Fire when event result is = True

I tested the regexp_replace by placing the contents of P27_weekday_selector in a temporary test field in a lower region and used P273_weekday_selector as the source. If I check the first three checkboxes I get the values 'Mon', 'Tue', 'Wed' in the temporary test field, but the chart is empty.
If I comment out the line with regexp_replace and un-comment the line above with the "hard-coded" ls.weekday in ('Mon', 'Tue') the graph displays properly.
How can I access the values stored in a checkbox so that I can reference them with the "In" clause? Is there another way to approach this?

Comments
Post Details
Added on Oct 22 2021
1 comment
2,562 views