|
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. |
