What are the requirements of an effective dated table
-
Define EFFDT
-
Define EFF_STATUS
-
Define EFFDT as key, in ascending order
-
Define EFFDT as key, in descending order
Effective dated tables require EFFDT as a key field in DESCENDING order. This ensures the most recent effective date (highest date value) appears first when querying, which is critical for time-based data retrieval. EFF_STATUS is often used but not strictly required. The key requirement is EFFDT in descending order as the first key field.
An effective-dated table tracks changes to data over time by keying rows with an effective date (EFFDT). For the current/most-recent row to be found efficiently (e.g., the first row matching as-of today), EFFDT must be part of the key and stored in descending order, so a simple lookup returns the latest applicable row first rather than requiring a scan through history in ascending order.