Re: Living without CONNECT BY

David Roth <droth_at_dr.ultranet.com> writes:  

>I have to convert some Oracle SQL scripts that use connect bys to ANSI
>standard SQL. Any suggestions would be appreciated.
 

What we had to do to expand a bill-of-materials in Rdb was to use a separate "scratch" table to collect the equivalent of the output from a START WITH ... CONNECT BY ... query.  

First, we delete any prior records in the scratch table.  

Then, we do an initial INSERT INTO scratch_table SELECT FROM main_table with a WHERE clause that is equivalent to the START WITH. We include a constant LEVEL_NUMBER of 0.  

We then loop in our programming language, incrementing the LEVEL_NUMBER variable. Each pass of the loop does another INSERT INTO scratch_table SELECT FROM scratch_table, main_table with a WHERE CLAUSE that does the equivalent of the CONNECT BY by doing a join of the scratch_table records from the previous level with the records in the main table, matching on "parent" and "child" keys. The current value of LEVEL_NUMBER gets inserted into the new records inserted into scratch_table.  

The loop quits when a pass finds no new records.  

When done, we do a final SELECT FROM scratch_table, including its extra LEVEL_NUMBER column.  

In order to let simultaneous users work with the same scratch table, we included a unique "process identifier" key as part of the table, so each user works only with their own subset of the scratch table data. We included an "insert date" as well, so a separate background job could clean out day-old trash from the scratch table from any crashed jobs that forgot to clean up after themselves.  

• Rick --

(Rick Rutt is a system architect living and working in Midland, Michigan.) Received on Fri Jun 09 1995 - 00:00:00 CEST