Weblog for the AMIS Technology corner
Posts tagged recursive subquery
The Knight’s Challenge – Recursive SQL Queries make a move on the Chess board
Apr 9th
In my quest to position ‘connect by’ (and its 11g successor, Recursive Subquery) as mechanism for recursive queries rather than just an hierarchical query facility, I stumbled across a simple, fairly meaningless challenge to take on: a knight on a chess board – and the question of finding its way from one square to another. 
Recursive querying in general consists of two elements:
- the initial situation, the starting point (the n=1 step)
- the algoritm to get from one stage to the next (the n to n+1 step)
A third, mandatory, element is the termination of the query: when is the journey complete, what is the maximum value for n or what is the exit condition. Read the rest of this entry »
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.
