Summary:
What is the difference between a Dynamic View and an SQL View?

Answer
SQL Views are actual database views that you create in Application Designer. To use one, you’ll need to build the view into the database and the Component processor refers to the database object whenever you access that SQL view. In contrast, a Dynamic View does not refer to a database object. PeopleSoft simply uses the SQL text that you put into the record definition. It does not try hard to understand your SQL, or parse it well to determine which actual db columns the columns in your record definition corresponds to.

I am only aware of one thing that you can do in Dynamic views that could warrant the use of it. That is you could put the meta-SQL %OperatorId anywhere in you SQL and the processor will dynamically substitute this value with the currently logged userid.

Aside from the problem you mentioned, problems also arise when your SQL has two or more tables joined by a field that is specified as a key in your record definition. In this case, the processor dynamically appends an ORDER BY KEYFIELD, without the correlation id, which makes your SQL invalid because KEYFIELD is ambiguous. Also another problem would be if your SQL has a WHERE clause. When adding criteria, the processor will add an open “(” after WHERE and a closing “)” at the end of your SQL. For most cases this won’t be a problem, but it will be if your SQL is complicated enough.

Unless you need to use %OperatorId, avoid dynamic views except for the simplest of SQL. If indeed necessary, the problem you mentioned could be solved by putting your original SQL inside a table expression:

Select RECFIELD1, RECFIELD2, …
From TABLE( Select DBFIELD1 AS RECFIELD1, DBFIELD2 AS RECFIELD2, … From DBTable) DBTBL

This is DB2 syntax. It may vary if you’re using other databases.

*Questions excerpted from ITToolBox.com*

Disclaimer: Contents are not reviewed for correctness and are not endorsed or recommended by PeopleSoft-Planet.com.