Solved

How to handle several WHERE clauses in a loop?

Posted on 2009-04-02
3
231 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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ejb stateless example 2 44
jsp CRUD operations with and without prepared statement also hibernatge 1 31
junit example issue 2 28
SQL query to select row with MAX date 7 43
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

730 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