A subquery is an embedded query inside a SQL statement that drives a specified behavior.
A majority of the PeopleSoft (PS) tables utilize effective dating through the use of the Effective Date (Effdt) field. Effective-dated tables provide a historical perspective, allowing you to see how the data has changed over time. Whenever users add a row of data to the table, they specify the date on which that data becomes effective or whenever users change a row of data, they specify a new effective date at which time the systems retains the previous version of the row as history in the table.
When you use a PeopleSoft (PS) effective-dated table, you will usually want the system to return the most current effective rows of data - the rows where the effective date is less than or equal to today's system date (sysdate). You may not want to see the historical rows, which are no longer accurate nor do you want to see future-dated rows, which are not yet in effect.
The process of retrieving only the most current effective dated record is accomplished by creating a subquery.
Note: Each table has its own parameters when creating a subquery.
This demonstration shows how to create a subquery on a PeopleSoft HR table (PS_Dept_Tbl).
To create a subquery*:
(*watch a video demonstrating these steps)