Issue in Multiple value selected in parameter

Question

I have created the multiselect List of value , it has 2 values - value 'A' and Value 'B'

1.if user selects only value A from the parameter then my query should fetch data 2.If User selects both A and B then query should display data 3.and if user selected only value B from parameter my query should not display data.

i have written oracle SQL query but it is not working.

Select data 
from myquery
where 
and ((:parameter)='A' or (:parameter) in ('A','B'))

the above query working for first 2 conditions. for 3rd condition it is not working if user select Value B it should not display data but it is displaying data

Any help is appreciated.

Answer 1

Prepare your query something like this:

((:parameter)='A' or ((:parameter) in ('A') and (:parameter) in ('B')))

I have created a SQL fiddle for this case to understand the issue. I have put all the cases for testing. You can uncomment each case and test. Please check the following URL: sqlfiddle.com/#!4/7d4a06/36

Answer 2

When you said

I have created the multiselect List of value

what does that actually mean? Which tool do you use so that it supports a multiselect list of values? I suspect that this might be Oracle Application Express (Apex) as it offers Select List items which allow users to select multiple values. Or, that could be Oracle Forms as well, but - nowadays - not many people use Forms.

Therefore, for this demo, let's presume that you really use Apex. In it, when user selects two (or more) values, they are separated by colon character. It means that if you select

• value A, Apex stores it as is: A
  • the same goes for other single values, e.g. B in your case
• two values, A and B, they are stored as A:B

In that case, where clause could check for existence of A because regardless of whether you select A only or A in combination with B (i.e. A:B), result should be true. On the other hand, if selected values is B only, result should be false and query shouldn't return any rows.

Query would then look like this:

select *
from your_table
where instr(:parameter, 'A') > 0
  and :parameter <> 'B';

---

Example is based on Scott's DEPT table; as I'm running it in SQL*Plus, I'm using substitution variable; disregard that, you'd use code as I posted above.

User selected A: display rows:

SQL> select *
  2  from dept
  3  where instr('&&parameter', 'A') > 0
  4    and '&&:parameter' <> 'B';
Enter value for parameter: A

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

User selected B: don't display anything:

SQL> undefine parameter
SQL> /
Enter value for parameter: B

no rows selected

User selected both A and B: display rows:

SQL> undefine parameter
SQL> /
Enter value for parameter: A:B

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

---

If that's not it and your "multiselect List of value" represents something different, what is it? Which values does it take when you select more than a single value?