Can you use a cursor variable in a WHERE..IN Statement??

From: Michael Green <DMichaelFSU_at_hotmail.com>
Date: 13 Jun 2002 13:31:38 -0700
Message-ID: <70aba55c.0206131231.24ac1535_at_posting.google.com>

[Quoted] [Quoted] Hi. I am trying to see if it is possible to use a cursor as the list [Quoted] of a WHERE..IN statement.

I have 20+ tables that hold hourly data for 7500+ different objects. I need to come up with a daily summary of the data in the 20+ table for each object. The 20+ tables only contain data and a key. A table, ENTRY_ID, relates the key to the date, time, object's name, etc. I am currently trying to use a cursor to query the ENTRY_ID table to get a list of keys for a particular object on a particular date and use that result set to summarize data in the rest of the tables. This way I only have to query ENTRY_ID once, not 20+ times for each of teh 7500 objects.

Eg: Replace this query:

       SELECT sum(t1.col1), sum(t1.col2), ...
       FROM table1 t1
       WHERE t1.id IN (SELECT e1.id
                       FROM entryid e1
                       WHERE e1.aDate = <date>
                         AND e1.obj = '<str>');

  with something like this:
       SELECT sum(t1.col1), sum(t1.col2), ...
       FROM table1 t1
       WHERE t1.id IN (id_cur)

How to setup up the cursor id_cur correctly to accomplish the same thing. Or is it possible? Or is there a different way?

Any and all help would be greatly appreciated. I am using Oracle 8.1.7 on Solaris 8

~~Michael Received on Thu Jun 13 2002 - 22:31:38 CEST