What’s the difference between NVL and NULLIF?
These two similarly named functions do very different things. Here’s a comparison.
What’s the difference between NVL and NULLIF?
These two similarly named functions do very different things. Here’s a comparison.
Here’s a quick start guide to taking and interpreting 10046 trace, with bind variables.
Tired of all the typing required to specify a date in a query using TO_DATE?
SELECT SUM(sale_amt) AS december12_sales WHERE sale_date BETWEEN to_date('2012-12-01','YYYY-MM-DD') AND to_date('2012-01-01','YYYY-MM-DD');
Whew! Shake out your fingers, that was a pain. Fortunately, there’s an easier way. Read more
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
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
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.
In my last post, I used CONNECT BY to query a table holding the bones of the skeleton in the “Dem Dry Bones” song: since the skeleton in the song has a head with no parent bone (making it a root node), and each bone has only one parent bone, it’s hierarchical data.
In this post, I’m going to add an arm and some ribs to the skeleton to make the data more interesting to query.
Read more
In my last post, I briefly covered what hierarchical data is and how it’s represented in an RDBMS using the adjacency list model. In this post, we’ll look at querying hierarchical data with Oracle SQL’s CONNECT BY construct, using a really fun example.
This is the first in a series of posts about Oracle SQL and hierarchical data.
Sometimes I think I might be the only person who’s more comfortable with Oracle’s original outer join syntax than with the ANSI standard. Read more