What are the requirements of an effective dated table?
-
Define EFFDT
-
Define EFF_STATUS
-
Define EFFDT, EFF_STATUS
-
Define EFFDT as key, in descending order
An effective dated table tracks data changes over time, requiring EFFDT (effective date) as the key field sorted in descending order. This ensures queries easily access the most current record first. Options A, B, and C are incomplete - defining EFF_STATUS or simply having the fields isn't sufficient. The key requirement is proper key definition and ordering.
An effective-dated table needs EFFDT (effective date) defined as part of the key, and the rows must be stored/retrieved in descending date order so that the most recent effective row as of a given date can be found efficiently (the first row with EFFDT <= as-of-date). Simply defining EFFDT (without specifying key/order) or EFF_STATUS alone is not sufficient — EFF_STATUS is a derived/supporting field, not the mechanism that makes date-effective lookups work. The combination 'EFFDT as key, descending order' is the actual PeopleSoft/Oracle pattern requirement, making it the most complete and correct answer.