Oracle 12c Row Pattern Matching: Beat the Best Pre-12c Solutions at OpenWorld!

Catchy title, don’t you think? My session has been moved to Monday 4 P.M., in direct conflict with Tom Kyte – and Keith Laker, who asked me to present in the first place. Avoid the lines: come see the MATCH_RECOGNIZE clause push great pre-12c solutions into retirement. As a bonus, be the first person on your block able […]

Rob van Wijk and ad hoc Grouping

In March 2014, I wrote about two methods for ad hoc grouping: “Grouping Sequences” and “Start of Group“. I just found out that Rob van Wijk wrote on the same subjects two months earlier. We even used the same example! I mention Rob’s article for two reasons: If my explanation wasn’t clear enough, you can try Rob’s. When […]

SQL and date ranges: don’t use NULL

We often use NULL values in date ranges, usually in the end date to signify the indefinite future. I have decided that this is a bad idea; here are my reasons. 1) NULL should not mean anything According to the SQL standard, NULL means “unknown”. That is why you can’t really compare a NULL value to anything, […]

SQL to find World Cup matches with comebacks

Lucas Jellema is writing a great series on SQL applied to the World Cup of football / soccer. In the article about finding matches with comebacks, he challenged readers to “find ‘dramatic comebacks’ where a team was two goals behind at some stage and yet managed to win the game.” Here is my reply, since replying […]

Bin Fitting problems with SQL

“Bin Fitting” or “Bin Packing” means putting the greatest quantity in the smallest number of “bins” or containers. There are some good solutions with the MODEL clause, but the most concise and efficient solutions use the new 12c MATCH_RECOGNIZE clause. There are two categories of “bin fitting” problems: The number of bins is fixed and the […]

Beyond Analytics: MODEL or MATCH_RECOGNIZE

Analytic functions are powerful and efficient, but sometimes they just aren’t enough. When you try analytics and they alone don’t solve the problem, it’s time to think about the MODEL clause – or upgrade to 12c and use MATCH_RECOGNIZE. All three can use partitions and ordering for simpler, more efficient processing. To illustrate, here’s a […]

Down with Firefox

Warning: this post is not technical and it is not about Oracle. Brendan Eich recently resigned under pressure from his job as CEO of Mozilla, the makers of Firefox. The reason given was a campaign contribution that Mr. Eich made in 2008. The State of California has the “referendum”: a proposition is submitted to a […]

Overlapping ranges with priority

A few years ago, Alberto Dell’Era blogged about product prices with overlapping date ranges; “on an overlapping range, the strongest priority (lower number) wins.” His analysis, explanation and solution are all excellent. I’m revisiting the subject to suggest a few improvements (and mention MATCH_RECOGNIZE yet again). To explain more easily, Alberto uses letters (a,b,c,d,e) instead […]

Merging contiguous date ranges

Last time I wrote about finding gaps in date ranges: this post is about merging date ranges that “meet”. This is a frequent question; the answer applies to any ranges, not just dates. As a reminder, I consider ranges to “meet” when the end value of one record is equal to the start value of […]