[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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
0
websteven
Asked:
websteven
2 Solutions
 
schwertnerCommented:
The way is to use Dynamic SQL.

In pure PL/SQL the vay is:

........
v_sql   VARCHAR2(487);
....

BEGIN
    v_sql := 'SELECT col1 FROM tbl_name WHERE ' || v_condition1 || v_condition1 || v_condition3;

    execute immediate v_sgl;
END;

When the query returns more then one row we use REF CURSOR and keep the values somewhere.
Possibilities are:

1. To return REF CURSOR to the calling software. Many interfaces support this
2. To keep results in PL/SQL associative array(s) and to return them
3. To keep in comma delimited string and to return the string, CLOB etc
4. To keep the results in a permanent or temporary working table

In most cases it depends if the interface you use is able to support these Oracle objects.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
You can even try as given in the below code flow :


declare
....
v_sql   VARCHAR2(32767);
n number;
....
 
BEGIN
    v_sql := 'SELECT col1 FROM tbl_name WHERE 1=1 ' ;
 
    n := 10 ;  -- assigning the number of where conditions...i have just hard coded.. you can 
               -- even fetch the same from some other table as to how many conditions are there
               -- and assign it to variable n
 
 ....
   condition_array := ...... ; 
   -- condition array will be populated here from some other table accordingly.
   -- and the number of conditions should be stored in the n variable above...
 ....
 
    for a in 1..n 
    loop 
 
     v_sql := ' and ' || v_sql || condition_array(a) ;
 
    -- condition_array will have all the conditions from which we can concatenate in the loop to the 
    -- sql statement before executing it...
   
    end loop;
 
    execute immediate v_sql;
 
END;
/

Open in new window

0
 
Geert GruwezOracle dbaCommented:
if the parameters are all for the same field
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)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now