Articles & Content


Six puzzles in SQL with Hierarchies and OLAP functions (E12)

Topic: 2010 EMEA

Subtopic: Cross Platform DB2 for z/OS & LUW

DATE: 2010-11-10 (14:15 - 15:15)
SPEAKERS: Michael Kamfonas (InfoKarta Inc.)

SQL is a powerful and often underutilized tool that can be used to solve some nagging problems easily and efficiently. We will spend 10 minutes on each of six common problems and their creative solution using SQL. We will demonstrate the underlying set-oriented ways of thinking and illustrate relevant SQL patterns and syntactical elements. We will also discuss applicability, performance and limitations. The six themes are:1. Hierarchies beyond recursion a. Topological ordering of a hierarchies (creating the boundary numbers) b. Variations on ancestor/descendent queries c. Data security from nested allow/disallow rules2. Creative use of OLAP functions a. Gap detection and contiguous counts (RANK, DENSERANK and ROWNUMBER) b. Versioning (LAG/LEAD) c. Inventory aging and cumulative SUMs

EXP. LEVEL: Intermediate,Advanced


Understand the technique of Topological Ordering (Nested Sets) for hierarchy traversal.

Understand how to use OLAP functions

Cultivate set-oriented thinking

Practical ideas where the techniques can be applied

Understand technique limitations, performance plus-minus and tips for tuning.

This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!

Download File
Click to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.