Re: Repeating a query and storing the results until no more records.

"Larus" <keith.hinde_at_btinternet.com> a écrit dans le message de news: 1138556537.623713.96070_at_z14g2000cwz.googlegroups.com...
| Hi (and help).
|
| I've been using Mysql and am in the process of migrating an intranet to
| Oracle. In the Mysql pages, we've created a number of pages which use
| temporary tables, which aren't available in Oracle.
|
| Most occurances I can rethink and improve, but I have one particular
| page which I can't fathom out a solution for.
|
| The ASP page carries out a Mulit-level Bill Of Material explosion. A
| parent part is entered, then the component parts for this part are
| read. These get stored in a temporary table and a master temporary
| table. The second read, uses the records from the first temporry table
| as the 'next-level' parent parts, and reads all of their component
| parts. These records replace the data from in the first temporary
| table, and get appended to the master temporary table.This continues
| through all levels of the B.O.M, until no records get returned. The
| looping is controlled by server-side scripting.
|
| The mastertemporary table is then used to create the data displayed to
| the user, after going back to the database to read other related data.
|
| The problem is the number of levels is not pre-defined. It varies
| according to the part being 'exploded'.
|
| Please can someone advise where I should looking. Is it Global
| Temporary Tables??
|
| Many thanks in advance.
|

Have a look at CONNECT BY clause.
It gives you your result in one statement. As you don't give us your Oracle version i can't point you to the correct documentation. Search on http://tahiti.oracle.com

Regards
Michel Cadot Received on Sun Jan 29 2006 - 12:21:03 CST