Monday, February 6, 2012

Make Room For One More!


Got some unexpected good news today. My company is sending a couple of developers to KScope12. I think what really put them over the edge was taking a look at the Schedule at a Glance

One other thing, ODTUG knows times are tough, so to ease the pain of registration, get a code from one of the vendors like Applied OLAP or the San Antonio Texas Oracle Users Group

C'mon, who doesn't want to save money??

Our journey continues...

Friday, February 3, 2012

WIDTH_BUCKET 101

Had an issue the other day where we needed to display counts per year range. The output needed to be something like:

[0-1] x dollars
[1-2] y dollars
[2-3] z dollars

The existing logic made redundant SQL calls to determine the min and max year vals. Very inefficient. Very unneccessary.

I immediately thought of using WIDTH_BUCKET. WIDTH_BUCKET is a numeric function that first appeared in Oracle 9i over ten years ago.


By definition, the Oracle WIDTH_BUCKET function is used to construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. For a given expression, the WIDTH_BUCKET function returns the bucket number into which the value of this expression would fall after being evaluated.

It's actually quite simple. Here's the syntax:

WIDTH_BUCKET(value, min_value, max_value, number_of_buckets);

I know what you're thinking, what if you have values that are below min_value or above max_value ?? Well, there's a convenient underflow and overflow bucket created automatically referenced by 0 and number_of_buckets +1, respectively.

Our journey continues...