Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!o13g2000cwo.googlegroups.com!not-for-mail From: "Catalin Pitis" Newsgroups: comp.databases.oracle.server Subject: Flattening a time-based tree structure Date: 8 Aug 2005 08:21:56 -0700 Organization: http://groups.google.com Lines: 38 Message-ID: <1123514516.292482.78040@o13g2000cwo.googlegroups.com> NNTP-Posting-Host: 82.77.120.46 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1123514520 9584 127.0.0.1 (8 Aug 2005 15:22:00 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 8 Aug 2005 15:22:00 +0000 (UTC) User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: o13g2000cwo.googlegroups.com; posting-host=82.77.120.46; posting-account=UTFZeA0AAAABAi7q8cTpya3rhA0XUJ8K Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:249101 Hi all I have to develop a job for transforming a hierarchy of users of the form: USER -------------- user_id parent_user_id valid_from valid_to (where valid_from valid_thru specifies the time interval the parent_user_id was the parent of user_id, but gives no warranty about the upper levels in the hierarchy) into a "flat" structure like this: USER_HIERARCHY --------------- user_id level_1_user_id level_2_user_id ... level_n_user_id valid_from valid_to There are about 10 millions records in the user TABLE and, unfortunately, I can't use CONNECT BY clause, because at some moment, the user U1 is the parent of user U2 and at another moment, the user U2 is the parent of user U1. The Oracle version I'm using is Oracle 9.2.0.5. Thanks, Catalin