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

Michael Green wrote:

> Hi. I am trying to see if it is possible to use a cursor as the list
> 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

I do not believe it is possible. But the simplest solution is to put a trigger on your 20+ tables and have a table that summarizes them.

Rant d'jour:
Whatever happened to relational databases being built with relational database software?
Whatever happened to the word 'normalize'?

Daniel Morgan Received on Thu Jun 13 2002 - 23:04:48 CEST