//AMIS Technology Blog » hierarchical query

Posts tagged hierarchical query

Vacatures bij AMIS services

ADF DVT Hierarchy Viewer on demand – made to order multi table hierachy view

0
1 year ago
by Lucas Jellema in , ,

This morning, I noticed the following email sitting in my Inbox: “Hello, Lucas.

I found Hierarchy Viewer demo from http://technology.amis.nl/blog/5786/adf-11gr1-new-hierarchical-viewer-for-visually-pleasing-representation-of-data-structures.

But i need to implement some kind of solution. I attached my expected mockup and table structure.

Is it possible? If possible, please suggest me how to do that and put your solution based on your experience.

I’m looking forward to hearing from you soon.”

Obviously, I normall completely ignore such emails – I do have a job you know, and some semblance of a normal life too. However, this email triggered me in some way. And between other more important things, I tried to create the desired hierarchy. The mock up looks like this:

The most challenging part is probably to get the query right. Once that query is defined – in a ViewObject with a selfrefencing ViewLink – creating the hierarchy is very straightforward. Some final styling is required – and a different design to the Radio Buttons, because those are not supported in the Hierarchy Viewer – but otherwise I think I did the job. My end result looks like this (from far away):

Note that the More >

SIG Event

Anti-Search patterns – SQL to look for what is NOT there – Part One

5
2 years ago
by Lucas Jellema in , , ,

Usually in SQL queries, you will try to retrieve information from a database by reading specific records from the relevant tables. However, there are situations where what you want to learn from a query is the fact that something is NOT there. So instead of querying for existing records, you are looking for records that do not exist. This article discusses a number of such situations and demonstrates several “anti-queries”. Where possible, I have used the EMP and DEPT table of the classical SCOTT sample schema to illustrate the point.

Some of these examples are perhaps better used to illustrate specific SQL features – such as CONNECT_BY_ISLEAF, PARTITION OUTER JOIN, LISTAGG, MINUS, SCALAR SUBQUERY and more – than to answer the questions at hand.

(more…)

SIG Event

Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring

3 years ago
by Lucas Jellema in , , ,

 

Oracle Database 11g Release 2 introduces the successor to the good old Connect By based hierarchical querying, called Recursive Subquery Factoring. The basics are described in a previous article: http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it. This article will show some additional examples of using this recursive subquery factoring syntax.

The essence of this recursiveness: the subquery consists of two queries unioned together. The first query returns the root-nodes, the starting points in the tree or network. The second query is used to continually retrieve the next step or level: it refers to the subquery itself and finds the next node level based on the levels (root and zero or more previously retrieved levels) already retrieved by the subquery.

In code:

(more…)

SIG Event

Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it

3 years ago
by Lucas Jellema in , ,

Many years ago, Oracle basically set the standard in SQL. Whatever was Oracle SQL could be seen as the standard. This has never been absolutely true – ANSI SQL was different from Oracle SQL. In some respects the differences can be traced back to lack of functional richness in the standard. However, in certain areas, Oracle has walked its own path with certain functions and features with the rest of the RDBMS pack following another route. It seems to be as if starting with Oracle RDBMS 9iR2, Oracle has made several important steps towards embracing the ANSI SQL standard syntax, usually while maintaining its own specific flavor of those same functions.

Some examples of Oracle specific syntax that are also available through their ANSI SQL counterpart – that in most instances has even more functionality – and that is almost always the preferred approach going forward:

  • outer join syntax: in addition to Oracle’s (+) notation, 9iR2 introduced the left outer join, right outer join and full outer join (like (+) on both ends of the join condition, something that Oracle does not support)
  • the Decode function that with 9iR2 can (and should) be replace by the CASE expression
  • the to_char More >
Go to Top