Solved

How to handle several WHERE clauses in a loop?

Posted on 2009-04-02
3
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

707 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