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

On 29 Jan 2006 09:42:17 -0800, "Larus" <keith.hinde_at_btinternet.com> wrote:

>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??

 Oracle has a hierarchical query extension that can do this sort of thing in one query, no temporary tables or multi-pass queries required.

 Look up "CONNECT BY".

 Temporary tables are rarely required in Oracle, but if you do need one, ensure you use a "global temporary table", created once along with the rest of the schema - _not_ created at runtime by the application. The _data_ in global temporary tables is specific to the session, whilst the _structure_ is fixed and shared.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool