Fear No SQL
After completing my article about skewness of a sample for Joel Kallman Day I decided I could do one more and write up the related functionality for skewness of a population. Like skewness of a sample, the purpose of the function is to indicate the degree and direction of asymmetry in a data distribution. Like…
Several years ago I needed to calculate the skewness of some data. That is, an indication of how asymmetric points are within a distribution. Unfortunately, I found there was no native feature to do this within SQL. So I wrote my own with the Oracle Data Cartridge Interface (ODCI.) Using ODCI you can create your…
One of the great features of Oracle is the ability to create a table function. That is, a function returning a collection type that can be used as the source for a query. These functions then allow a developer to provide a simple SQL api to query rows and columns from a variety of data…
Recently, I had a satisfying conclusion to a series of questions that came in randomly over the course of a few weeks. First the developer was trying to do recursion. I pointed to documentation and gave a couple of small examples showing CONNECT BY and recursive WITH clauses. SELECT LPAD(‘ ‘, LEVEL * 2) ||…
Part 2Part 3 Yesterday, a coworker asked me a question just out of curiosity: “How would you determine which words can be typed with alternating hands?” He then added an additional criteria that he’d like to find the words that ended on the left hand (presumably, so you could press space or enter with the…
Previously, I’ve shown how to use a user-defined aggregate to process numeric values; but they can be used for text as well. Recently I was tasked with importing some denormalized text data and aggregating into a consistent form. The source data consisted of many rows holding identifiers, multiple comma-separated values (CSV,) and several other fields.…
A common occurrence in sql is to aggregate text values into a larger, delimited value. It can also occur that you have a delimited value and you want treat it as a collection to pull data out of it process each of the sub-values. Oracle has a built-in function LISTAGG to aggregate text values into…
In my previous post I showed how to use the MAP member functions to compare two objects of the same class. The MAP functions are useful when an object can be meaningfully represented and compared by a single value, especially if that value might provide functionality or meaning itself (such as the triangle area mapping…
If you have 2 instances of an object, A and B, you might want to compare them: is A > B or B > A? By default, two instances can’t be compared for greater or lesser value, only for equality. Where equality is determined by checking each attribute of each instance in order and comparing…
When an object type is instantiated (i.e. assigned a non-null value) it will execute initialization code. This initialization code is called a CONSTRUCTOR function. Every object type includes an implicit constructor which simply assigns values to the object attributes according to the values provided in the object assignment. For example: CREATE OR REPLACE TYPE simpleobject…
A lot of developers see Object Types as confusing, esoteric, or simply unrelated to their SQL work. To be fair, some objects can be quite complex and some of the data cartridge (ODCI) features will never find a home in some developer’s applications. But, that still shouldn’t preclude an examination of some of the fundamentals…