Earlier this week I got tangled up doing a Roman Numeral conversion in my head. So of course my second thought, right after “Doh!”, was “I bet I can write a SQL statement to do this for me next time.”
Read more
Tag: recursive with
Recursive WITH, part III: IS_LEAF
The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifes leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.
Read more
Recursive WITH, part II: Hierarchical queries
In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH.
Read more
Recursion with recursive WITH
I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that I'm not making much use of the developments in Oracle SQL since 8i. In this blog post, I'll look at the recursive subquery factoring introduced in Oracle 11g.