Solved

How to handle several WHERE clauses in a loop?

Posted on 2009-04-02
3
228 Views
Last Modified: 2012-06-21
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
Comment
Question by:websteven
3 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 24047319
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 250 total points
ID: 24048303
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24049435
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question