Solved

How to handle several WHERE clauses in a loop?

Posted on 2009-04-02
3
224 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
Comment Utility
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
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
The viewer will learn how to implement Singleton Design Pattern in Java.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now