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.
Software/Hardware used:
ASKED:
January 3, 2008 11:09 PM
UPDATED:
January 23, 2008 11:42 PM