Recursive SQL for Data Normalisation

Recursive SQL can be awesome, although a bit hard to read in its SQL standard beauty. Let's assume you have some aggregated data with dates and a number of events per date: | DATE | COUNT | |--------------------------------|-------| | October, 01 2013 00:00:00+0000 | 2 | | October, 02 2013 00:00:00+0000 | 1 | | … Continue reading Recursive SQL for Data Normalisation

Emulating the SQL standard derived column list

Derived column lists are a fine feature, if your database supports them. The SQL:2008 standard specifies 7.6 <table reference> <table reference> ::= <table factor> | <joined table> <table factor> ::= <table primary> [ <sample clause> ] <table primary> ::= <table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> … Continue reading Emulating the SQL standard derived column list

Serious SQL: A “convex hull” of “correlated tables”

Now THIS is an interesting, and challenging question on the jOOQ user group: https://groups.google.com/d/topic/jooq-user/6TBBLYt9eR8/discussion Say you have a big database with lots of tables and foreign key references. Now you would like to know all tables that are somehow inter-connected by their respective foreign key relationship "paths". You could call this a "convex hull" around … Continue reading Serious SQL: A “convex hull” of “correlated tables”

Recursive queries with Oracle’s CONNECT BY clause

Recursive or hierarchical queries are an awkward thing in SQL. Some RDBMS allow for recursiveness in Common Table Expressions (CTE's), but those queries tend to be quite unreadable. That's not the case for Oracle, which, in addition to recursive CTE's also supports a dedicated CONNECT BY clause. The general syntax for this clause looks something … Continue reading Recursive queries with Oracle’s CONNECT BY clause