websteven
asked on
How to handle several WHERE clauses in a loop?
Hi!
I have a loop where I determine how many WHERE conditions I will need. It may return 0 or 3, or something else.
The Where conditions are Strings, that I hold in a List or an array.
But how do I pass these parameters to the SQL statement, as it always may differ in the counting?
Do I have to loop over the Statement and hold it in an array?
Is there a better way to solve this?
Thanks for your help
I have a loop where I determine how many WHERE conditions I will need. It may return 0 or 3, or something else.
The Where conditions are Strings, that I hold in a List or an array.
But how do I pass these parameters to the SQL statement, as it always may differ in the counting?
Do I have to loop over the Statement and hold it in an array?
Is there a better way to solve this?
Thanks for your help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you could create a table to hold the values for the parameters and some unique id to go with it
CREATE TABLE TESTPARAMS (
ID NUMBER(10, 0),
VALUE VARCHAR2(100));
INSERT INTO TESTPARAMS (10, 'BLUE');
INSERT INTO TESTPARAMS (10, 'RED');
INSERT INTO TESTPARAMS (10, 'GREEN');
and then in your select just give the ID of the TESTPARAMS:
SELECT * FROM COLORS
WHERE COLORNAME IN (SELECT VALUE FROM TESTPARAMS WHERE ID = 10)