sql-programming the ‘in’ statement

5 pts.
Tags:
Coding
SQL
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.
ASKED: January 3, 2008  11:09 PM
UPDATED: January 23, 2008  11:42 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following