Solved

How to handle several WHERE clauses in a loop?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Query with Function 3 52
SQL2016 to ORACLE11G linked-server 6 28
hashmap order 17 36
making a message body variable from an oracle select statement 4 23
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

808 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