Group by Groups

For some time now I have struggled to efficiently "group by groups" on data containing references between our suppliers item numbers and the original equipment manufacturer (OEM) number. I can group those data by supplier and their item number and get a "group of OEM numbers." That group I will call a unique item and I will group the data once more by that group to find which suppliers agree on an item having the same set (group) of OEM numbers.

It took me some time to figure out how to do this efficiently (the data was several million (more...)

Fiddling with MONTHS_BETWEEN

A few days ago I answered a question on OTN SQL and PL/SQL forum that prompted me to fiddle around with MONTHS_BETWEEN. I did discover something new that I wasn't aware of while developing an alternative MONTHS_BETWEEN implementation.

The forum poster had a requirement where employee allowance was to be calculated based on a from and to date. A fixed monthly allowance was given which then could be multiplied to get the total allowance, but if 20 days had been worked in April allowance was to be 20/30 of the monthly figure, while 20 days in May would give 20/31 (more...)

Going to UKOUG 2012

My presentation on analytic functions has been accepted for UK Oracle User Group Conference 2012 ( #UKOUG2012 ) - awesome \o/

Now I just need to tighten up my show and remove all the beginner bits - I've only got 45 minutes for something I could easily use 1½ hour for ;-)

See you all in Birmingham in December...

INSERT ALL master/detail data from XML

This is something I actually made for a quiz on PL/SQL Challenge, but I think the technique could be useful for others as well :-)

The idea is you may have some master/detail data (in this case orders and orderlines) for which you get XML with such data that needs to be inserted into two relational tables. Many might be tempted to parse the XML client side or in PL/SQL, loop through the data, and then insert the orders and lines row by row (or perhaps bulk insert from arrays.)

But it can be done in a single (more...)

Conway’s Game of Life in a MODEL clause

This post has no serious purpose. I was just fooling around with the MODEL clause when I got the idea that ITERATE could be used for modelling Conway's Game of Life. So that's what I did - just a little fun example of what MODEL can be used for. Sure it could be done in any number of other ways, I don't claim this to be a smart or efficient way, just fun ;-)

Conway's game of life models a population of cells in an twodimensional coordinate system with rules for how cells die, survive or reproduce from generation to (more...)