Archives

Tagged ‘analytic functions‘

ODI and analytic functions (again???)

My friend and colleague Maciej Kocon has come up with the following trick. Rather than use a user defined function to implement an analytic function such as MAX() OVER or SUM() OVER as suggested by note 807527.1 there is another way to work around the getGrpBy bug.

We will take advantage of the ODI multi pass parser and hide the analytic function from getGrpBy in our mappings:

<?out.print(“SUM”);?>(SALES.AMOUNT_SOLD) OVER (PARTITION BY PRODUCTS.PROD_CATEGORY)

The above SUM gets only generated once getGrpBy has been parsed and as a result no GROUP BY clause is generated, which is the correct behaviour.

A brilliant yet simple trick.

Cheers Maciej.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Comparative Window Functions: Proposed Extensions to Analytic Functions

I promise that this will be the last post on analytic functions this month; but I came across this proposal to extend analytic functions with a comparative window function on Tom Kyte’s blog yesterday (also make sure to review the actual paper). Basically this new type of analytic function would allow you to compare the current row in a window to any other row in the window. I recently had a requirement where this would have come in handy. Imagine a dataset made up of product category, product id, product referential id, e.g.:

Computers,1,2
Computers,1,3
Computers,1,4
Computers,4,9

The requirement was to find those products whose reference products were also in the dataset. In the example above this would have been the record Computers,1,4. Something that can only be easily solved with a self-join. With this new proposal you could write this as

SELECT * FROM (
	SELECT
		prod_cat.
		prod_id.
		prod_ref_id,
		MAX(CASE WHEN prod_ref_id = INDEX(prod_id,anchor_row) THEN 1 ELSE 0) OVER (PARTITION BY prod_cat ROWS BETWEEN UNBOUNDED PRECEDING UNBOUNDED FOLLOWING) ref_exists_ind
	FROM
		prod_ref
)
WHERE ref_exists_ind = 1

Pretty cool…

CSV to Rows

I recently needed to convert a comma separated list of values in a table column to a column where each value in the list gets its own row in a table.

So first of all let’s create such a table. I have limited the number of comma separated values that will be created to 12.

View Listing 1

To convert our csv list to rows we will create an in-memory helper table that for the maximum number of comma separated values has a corresponding number of rows. We can then use this helper table to join to our main table and then parse the values.

The SQL below will create our helper table:

View Listing 2

Results for the helper table will look like:

Now it is simply a matter of joining our main table to this helper table and parsing the results:

View Listing 3

In order to improve performance we could create a function based index for our join column.

There are other solutions that will do the same thing and requires less code, but will not perform as well. So if you have a small set of rows to convert you can use this solution: How do I split comma delimited data in a SQL Statement