CODEWITHSUNDEEP
plsqloracle-apexoracle-apex-5oracle-apex-5.1
Abdirizak Obsiye
Abdirizak Obsiye

Reputation: 285

Oracle Apex 5 - Checkbox list in where clause not working, why?

I have a quirky issue. I have a checkbox, a reports table and i'd like to click a checkbox or multi check boxes to return a result. In the Database I have a saved list of checkbox results seperated by a colon :

It all works except when I check box 'A' and 'C'. which is the first and last result in the database. I can click A and B or just A or just C or C and B but when i click A and C it doesn't work.

any clue?

select ...

from ...

where 

AND (CASE WHEN :P1_BENEFITS_TAG IS NOT NULL 
     THEN instr( ':'|| UPPER(b.BENEFITS_TAGS)||':',
                 ':'|| UPPER(:P1_BENEFITS_TAG) ||':' )
     ELSE 1 
     END) > 0

enter image description here

Upvotes: 0

Views: 451

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132670

It looks like your database row contains the string 'A:B:C:'.

When :P1_BENEFITS_TAG is any of the values 'A:B', 'B:C', 'A', 'B' or 'C' then the database row "contains" that value and your INSTR expression will return a number > 0.

However, 'A:B:C' does not contain the string 'A:C', so the INSTR will return 0 for that.

Upvotes: 2

Related Questions