Question

sql-programming the 'in' statement

SQL, Coding

I'm working on a task ; the issue is:
There are number of criteria for each category; the relation between the criteria's is OR and between the category is AND. The CSR can select number of criteria for each category (or not select anything); I supposed to get as an input all the selected criteria and it's category and need to retrieve data according that. The problem is to ask about concatenating value with "IN" option:
CATEGORY IN(:sqli_plan_type_criteria)
case if I will insert a concatenating values the sql refer it as one string!!:
CATEGORY IN('xxx,bbb,ccc')
let me know what the best way to do it;
I checked some solution:
1. Dynamic DL - not practical; because the query is huge one.
2. Using INSTR - Not good enough because it's make the query heavier ( ADBA expression).
3. Using number of parameters;
CATEGORY IN(:sqli_plan_type_criteria1,
:sqli_plan_type_criteria2,
:sqli_plan_type_criteria3,
:sqli_plan_type_criteria4)
but, in this way, I don't know how many parameters to insert, I need to set the number in advance, it's difficult to maintain.
4. Using function called "split", which take string with values separated with "," and split it into separate values. In order to use it you need to use in the statement: table(split('the string that you have').

please assist me, what the best way, or do you know different better way.

basic strategy is to use parenthesis to separate the and's and or's

(condition-1 and condition-2 and condition-3)
OR
(condition-4 and condition-5 and condition-6)

repeat for as many sets as you have
this can also be nested - if needed but put in your own lables to keep the levels straight

-- LEVEL ONE
( some-condition

OR
( -- LEVEL two conditions)

OR
( -- LEVEL two conditions)

)

and so forth