Developing Time-Oriented Database Applications in SQL
An enjoyable plunge into the how-to of time and data, with real-world situations and code.
TABLE OF CONTENTS Developing Time-Oriented Database Applications in SQL
Chapter 6: Querying State Tables
OVERVIEW
With the history stored in a valid-time state table, the most prevalent queries are still of the form "What is true now?" Such current queries are easy to convert into SQL. A related kind of query is, "What is true at a point in time other than now?" These time-slice queries are also easy to convert.
For each current query, for example, "Who makes more than $50,000 annually?" there is an analogous sequenced query that asks for the history, for example, "Who makes or has made more than $50,000 annually, and when?" Converting such queries to SQL can be challenging.
In the previous chapter, we saw that there are three kinds of constraints you can specify for a temporal table: current, sequenced, and nonsequenced, with nonsequenced constraints the easiest to specify yet the least useful. A similar situation exists for queries and modifications applied to temporal tables.
Here we examine common queries over temporal tables, from extracting the current state, to extracting previous states, to evaluating several kinds of sequenced queries. In the following chapter, we then turn to modifications, examining the three variants there. We assume that the INCUMBENTS and SAL_HISTORY tables are valid-time state tables.
6.1 EXTRACTING THE CURRENT STATE
| Tip | Executing a query on the current state of a temporal table requires an additional predicate. |
Queries on the original table (before it was timestamped to render it as a valid-time table) correspond to queries that extract the current state...
Copyright Morgan Kaufmann Publishers, Inc. 2000 under license agreement with Books24x7
