2012-12-27
blank
2012-12-27
Categories

Merging Temporal Intervals with Gaps

In Joining Temporal Intervals I explained how to join multiple temporal tables. The provided solution merges also temporal intervals but – as pointed out in that post – may produce wrong results if the underlying driving table is not gaplessly historized. In this post, I’ll explain how to merge temporal intervals with various […]
Do you like it?
1 Read more
2012-12-24
blank
2012-12-24
Categories

Joining Temporal Intervals

From time to time a customer asks me how to join multiple tables with temporal intervals (e.g. defined by two columns such as valid_from and valid_to per row). The solution is quite simple if you may limit your query to a certain point in time like now, yesterday or similar. Such a […]
Do you like it?
1 Read more
2012-12-23
blank
2012-12-23
Categories

Building Comma Separated Values with Oracle & SQL

From time to time I’m asked to aggregate strings from multiple records into a single column using SQL. Here’s an example, showing a comma-separated list of ordered employee names per department based on the famous EMP and DEPT tables. Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If you […]
Do you like it?
2 Read more
2011-10-12
blank
2011-10-12
Categories

Using UTL_XML.PARSEQUERY for SQL Dependency Analysis

Last week I had a talk at Oracle’s OpenWorld 2011 titled Modern PL/SQL Code Checking and Dependency Analysis. The problem I described in chapter 4 was to find all view columns using the column UNIT_COST of the table COSTS in the SH schema. Other usages of this column (e.g. in where or […]
Do you like it?
9 Read more