Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

If you have ever tried to dig into the PeopleSoft HRMS* view definitions related to row-level security, you have seen many "meta-SQL" clauses. These begin with "%SQL" and they are references to SQL objects. A SQL object is simply a piece of SQL text (possibly including other %SQL clauses). The %SQL clause is replaced with the text from the SQL object.

This is a simple concept. The difficulty comes in trying to read SQL that contains numerous nested %SQL clauses. Also, SQL objects can receive parameters. These are referenced as %P(1), %P(2), etc. A %SQL clause can then pass parameters to the corresponding SQL object. The value of each parameter replaces its %P() marker. This increases the flexibility of SQL objects, allowing them to be reused in several places. But all of these nested and parameterized references can be hard to follow.

Fortunately, there is an easy way to expand all of the SQL. Open the view definition in App Designer. Right-click on the text and select "Resolve Meta SQL" from the pop-up menu. All of the %SQL clauses will be replaced and you will be able to read the entire view definition as normal SQL.

Note that SQL objects are also used in PeopleCode to instantiate SQL objects in memory. These can then be used in issuing a database query or update operation. But it is probably more common to see them used in view definitions.

* Yes, I know that it's called HCM now. But I haven't been able to make the transition from calling people "resources", which is bad enough, to calling them "capital". I'm waiting for the next version, which I believe will be called HLM, or "Human Livestock Management". We already do headcount reporting—what could be more appropriate?