Posts

Showing posts from March, 2007

Flatten Out a Heirarchy using SYS_CONNECT_BY_PATH

In a recent application, we needed to model the Organisational Hierarchy which at its most complex ran to 7 levels deep. This was achieved using a self referencing foreign key (Pigs Ear) similar to that of the EMP table in the Scott schema. In essence, it simply stores the parent / child relationship for each entry in the Hierarchy. This approach serviced the application very well in that a simply tree walk (CONNECT BY PRIOR) allowed us to construct the Hierarchical tree and bounce our requests off that. During the production of the Discoverer End User Layer, it became evident that this Hierarchy needed to be flattened out (un-normalized) in order for it to reported on. This is because Discoverer (or any other BI product) does not support the CONNECT BY and START WITH clause. Discoverer needs to know how many levels exist within a Hierarchy and that every thread in the Hierarchy has the same number of levels in order to build a folder structure that can be reported on. After a bit of i

Translate Columns into Rows (Subquery Factoring)

It has always been a fairly rudimentary task pivoting a result set so that rows are displayed as columns. More recently I had the requirement to translate a result set the other way so that the columns would be displayed as rows. For Example Your result set starts out like this: SITE COST1 COST2 COST3 COST4 ------------------------------------------------------------------ SITE_ONE 2000 255 SITE_TWO 100 SITE_THREE 145 5000 The desired output should look like this: SITE VALUE ------------------------------ SITE_ONE 2000 SITE_ONE 255 SITE_TWO 100 SITE_THREE 145 SITE_THREE 5000 Thanks to the help from Mr. Tom Kyte at http://asktom.oracle.com I was a