Creating Views

Views in PeopleSoft applications are comparable to views used in traditional relational database management system (RDBMS) technology. A view, in essence, is a virtual table. It is created from the results of a query run against one or more tables.

When you create views to use online, you must keep the views synchronized with the database. Like SQL tables, you must have a record definition for online views because the system checks the record definitions for online processing rules. You must build the view before you can use or reference it online.

If you use a view as the basis for a page, you select existing information to display on the page. Therefore, when you create the record definition for your view, you can clone an existing record definition, delete any fields that are not used in the view, and proceed to define the view. Query views are processed the same way as view text, which is defined as part of the record.

The order in which PeopleTools creates views is based on the build sequence number that you set on the Record Type tab while the record is open. The default build sequence number is 1. For the dependent views to be created first, the build sequence number should be 0. This way, all of the 0 views are created first, then the 1 views, and finally the views that are greater than 1. The maximum build sequence that can be set is 99. Although the views are sorted alphabetically in the project, they are created based on the build sequence number.

If you are already familiar with creating record definitions and writing SQL queries, creating online views is similar.

To create online views:

1. Clone a record definition:

   1. Open an existing record definition.

   2. Select File > Save As.

   3. Specify the name of the view that you want to create.

2. Delete and add the appropriate fields.

   All columns that are selected in your view should have a corresponding field in the record definition.

3. Set the record type to SQL View:

   1. Select the Record Type tab for the record definition.

   2. Select SQL View in the Record Type group box.

4. Click the Click to open SQL Editor button.

   The order in which you list the columns in your SQL SELECT statement should match the order that you specified in the record definition. A SQL statement can be given for each platform.

   See Creating SQL View and Dynamic View Select Statements.

5. Save the record definition.

6. Select Build > Current Definition to create the view in the database.

7. Select the Create Views check box under Build Options.

8. Click Build.

If you plan to use a join regularly, you can save it as a SQL script or you can create a view—a single virtual table—using the join as the logical representation of the data. After you create a view, the users never need to know that the data they are viewing is stored in multiple tables. To users, the relative complexity of your view is transparent. For example:

SELECT A.EMPLID,
       A.NAME,
       B.CONTACT_NAME,
       B.RELATIONSHIP
FROM   PS_PERSONAL_DATA A,
       PS_EMERGENCY_CNTCT B
WHERE  A.EMPLID = B.EMPLID;

PeopleTools includes functionality to produce meta-SQL constructs for dynamic views, which means that dynamic SQL views that are created using PeopleSoft Application Designer can be used on any platform that is supported by PeopleSoft. The PeopleTools: PeopleCode Developer's Guide contains a full list of the meta-SQL that is supported in the PeopleSoft Internet Architecture.