John_K wrote: (in regards to Reply 13)
So pretty much exactly what was suggested in reply 2 of this thread then?
Not exactly. If you look at the two solutions more closely, you will see that - while they look very similar - there a few differences.
The solution in Reply 2 looks for rows with the date in September or October of ANY year, while Reply 13 looks for a specific year. More importantly, since the dates are now specific, the answer in Reply 13 does not need to wrap the date value within any function, which may result in better performance.
The solution in Reply 13 allows for more than one transaction date in September; Reply 2 assumes there is at most one (it checks a count = 1 instead of > 0). It seems that > 0 would be the desired answer, in general.
I've written a couple of blog notes based on this question because it was a nice one to demonstrate a couple of traps with match_recognize(). (That doesn't mean match_recognize() is a bad thing, only that, like many other Oracle features, you always have to ask yourself which cases it is good for and which it is bad for, and how best to use it when you use it.)
Part 1: Sorting and matching
Part 2 was in response to Stew Ashton's comments on part 1, and he was also the author of the efficient solution.
Thank you for very much for the blogs. Didn't realize at the time of writing, it will draw this much of scrutiny. Learned quite a few things from your posts though.