Thursday, October 18, 2012

RANGE BETWEEN and leap years

Answering a question on the OTN forum was a bit tricky to get an analytic sum using a RANGE BETWEEN that would handle leap years, but in the end I came up with a workaround that satisfies the requirement. Along the way I realized why there are two different INTERVAL datatypes :-)

Wednesday, October 17, 2012

Find your way with HttpUriType and Google Maps

Recently I read Duke Ganote writing about using UTL_HTTP to get stock quote from Yahoo. (Duke must have a thing for authorities, particularly Marshalls of Legoredo ;-) Anyway, I posted a comment how to do a similar thing with HttpUriType.

And that reminded me that long time ago I reminded myself that I should blog about how we use HttpUriType to query driving distance and time from Google Maps. (I have even tried to submit abstract to KScope and UKOUG on getting data with HttpUriType, UTL_HTTP or UTL_FTP, but no go so far...)

Monday, October 8, 2012

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 records and the "unique groups" to be found was about 3/4 million groups.) But after several tries, some good help from the OTN forums and some reading in my Tom Kyte books, I finally got a solution that seems to work well ;-)

Thursday, October 4, 2012

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 of the monthly figure.