querying against Result Sets.

[Tad Dicks]

Is it possible to run a query against a resultSet? As opposed to getting a resultSet, creating a new table, and then querying against that or creating a new query from two?

I have to write code that will allow a user to create as series queries each one run against the previous queries results (so that the queries are against progressively smaller and smaller data sets).

If it makes any difference I'm using HSQLDB.

-Tad

[Tad Dicks]

Having looked around I'm guessing the thing to do would be to create "views."

This may be a relatively basic question... but when you create a view, how long does it persist(permanently, until you close the connection)? if you create a view how widely available is it (only to the connection that created it or any connection?) If they persist, do you dispose of them the same way you would a table?


-Tad

[Jeanne Boyarsky]

Originally posted by Tad Dicks:
Is it possible to run a query against a resultSet? As opposed to getting a resultSet, creating a new table, and then querying against that or creating a new query from two?

No. You would need to use a temp table, materialized view or do the query again and use a join.

when you create a view, how long does it persist(permanently, until you close the connection)? if you create a view how widely available is it (only to the connection that created it or any connection?) If they persist, do you dispose of them the same way you would a table?

If you are using a materialized view (available in Oracle) the view is persisted. Take a look at this article for more details on materialized views. It is available to any connection as it resides on the database. The availability depends on the settings used when it is created.

A regular view is like a shortcut syntax for a query. It does not save the results at all. The database expands it into the real query at runtime.