Help Center
Having Trouble? We are here to help.
Call: 1-855-ASU-5080 (1-855-278-5080)

Subqueries (Hy11)

Last Updated:  1/17/13 9:11:11 PM 8373-8193-7061

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)

  1. From the Query section, locate the PS_Dept_Tbl and drag it into the workspace..

  2. Add the desired fields from the PS_Dept_Tbl table into the Request line.

  3. To access the subquery option, we'll first need to add a filter to the Effdt field. Drag the Effdt field from the table into the Filter line.

  4. In the Filter: Effdt window, click on Advanced.

  5. When the Advanced options appear, click on Create Subquery.

  6. A subquery workspace will appear (indicated by a gray background). Also note you are on the "SubQuery" section listed in the Sections pane on the left.

  7. Right click on the Effdt field within the Request line. Select Data Functions> Maximum.

  8. In the Elements window, click on "+" next to Queries to expand the list.

  9. Select Query and drag it into your subquery workspace.

  10. Next we'll join the Setid and Deptid fields from the PS_Dept_Tbl to the correlated Query. In this example, the Setid and Deptid are the table's primary fields.
    Note: The primary keys will vary depending on the tables and the fields to join will also vary depending on the data needed.
  11. Drag the Setid field from the PS_Dept_Tbl to the correlated Query. On the Select correlation column window, select Setid and click on OK.

  12. Drag the Deptid field from the PS_Dept_Tbl to the correlated Query. On the Select correlation column window, select Deptid and click on OK.

  13. Next we'll add a filter to Effdt to exclude any future dated records.
  14. Locate the Effdt field in the PS_Dept_Tbl table and drag it into the Filter line. On the Filter window for Effdt, click on the Custom SQL button.

  15. In the definition box, type in ≤sysdate so it reads Ps_Dept_Tbl.Effdt≤sysdate. Click on OK.

  16. Return to the main query by selecting Query in the Sections pane. From the Query secion, click on Process.



Improve the Help Center. Provide Feedback.
Did this article resolve your issue? *