Dynamic Views

Dynamic views are SQL statements executed on the fly by the PeopleSoft component processor. This is different to a regular PeopleSoft view which is a database view that needs to be built and exists in the PeopleSoft database.

So why use dynamic views? Well, they are really handy for situations where you need slightly different combinations of the same underlying SQL. They are also good for really simple views that you don't want to store in the database. Another useful application is to use them as hollow views for application packages.

However the best application I have found for dynamic views is when you have the scenario where you have slightly different combinations of the same underlying SQL and you need different key/search key/alternate search key combinations.

Here's an example using Campus Solutions. Say you have a prompt table that caters for four scenarios:

  • Scenario 1, the user specifies both campus and academic group
  • Scenario 2, the user specifies campus but does not specify academic group
  • Scenario 3, the user does not specify campus but specifies academic group
  • Scenario 4, the user does not specify campus or academic group

This can be simplified into a truth table based on the fields campus and academic group like so:

Scenario Campus Academic Group
Scenario 1 True True
Scenario 2 True False
Scenario 3 False True
Scenario 4 False False

You could create four views and alter the keys and SQL accordingly. However, you would then have four almost identical views that don't really serve any purpose in the database other than for your prompts.

The other solution (provided the view is not too complicated) is to create four dynamic views, each with the appropriate keys and SQL and use these as prompts. These only exist as objects in the PeopleSoft database and don't need to be built as database views. In this respect, dynamic views are really about minimising development overhead.

One very important thing to note about dynamic views - they are limited in the complexity of the SQL they can handle. Typically, you should be selecting from one underlying table/view as joins may not always work or cause performance issues.
No Comments
Back to top