Weblog for the AMIS Technology corner
Posts tagged hierarchy
Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
Nov 14th
On our blog, we have been discussing the new hierarchical query functionality in Oracle Database 11g Release 2, using Recursive Suquery Factoring. Instead of using CONNECT BY and its close associates such as START WITH, PRIOR, LEVEL and more exotic comrades like SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and NOCYCLE this release gave us a new, less proprietary and eventually probably more intuitive and functionally rich approach. We have also written how though we have no straightforward alternatives for LEVEL, SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT – in the new recursive approach they are fantastically easy to emulate.
Until recently I have been quite happy with the new hierarchical querying and telling the world how I felt. Then an esteemed colleague – a far more experienced SQL programmer than I am – came up to me and remined me how the recursive sub query syntax at the present does not have a replacement for the CONNECT_BY_ISLEAF function – the SQL function that tells us whether a node produced in an hierarchical query has any children or is at the bottom of the chain – i.e. a leaf node. For leaf nodes (child-less), the function returns a value of one and for parent nodes the value is zero.
Anton (my colleague) was right and unfortunately I did not have a quick retort. However, after giving it some thought I believe I have found a way of emulating the CONNECT_BY_ISLEAF as well, using the new DEPTH FIRST ordering capabilities of the recursive subquery. I hope this will satisfy Anton as well.
ADF 11gR1 – new Hierarchical Viewer for visually pleasing representation of data structures
Jul 7th
One of the data visualization tags required by the teams working on the Oracle Fusion Application Module for Human Resource Management, was a component capable of rendering organization charts. Hierarchical structures from CEO all the way down to the youngest trainee. In a pleasing, graphically interesting, somewhat animated fashion. And so the ADF team developed the Hierarchy Viewer. And since they developed it anyway, we can now make use of it as well. While it may not be the component you will most frequently use, it is certainly an interesting presentation option for special data structures. This component can work against the same tree data binding you would use for tree tables or trees, and can therefore be configured in a very simple, declarative fashion.
In this article some simple examples of how to use this new component. This article is the short summary of a presentation and demonstration I did at the recent ODTUG Kaleidoscope 2009 conference (late June, Monterey). It demonstrates how the conference’s session schedule can be represented in the Hierarchy Viewer.
Creating a Read Only Hierarchical Table Report with ADF 10g
Nov 9th
Presenting information to end users can be somewhat challenging, especially if you want to provide a lot of information on a single page in a structured way. You can swamp the users with information and data, claim all they need to know is there and be on your merry way (that is the way most Excel sheets look to me). On the other hand, creating the perfect Dashboard can be a very time consuming task. In this article I try to describe a simple, structured and rather effective way of presenting a lot of information – I did not devise this presentation, that’s from one of my current customers – that can be implemented fairly rapidly. Perhaps it can provide some inspiration.
