Tuesday, October 14, 2014

The new %SelectDummyTable MetaSQL

Does anyone know a PeopleSoft developer who didn't ever use a SQL statement like the following one?

select %CurrentDateOut
from PS_INSTALLATION;

Where PS_INSTALLATION could be any single-row table in the PeopleSoft data model.

If you look at the previous statement, the SELECT clause is not retrieving any field from the PS_INSTALLATION table, but just using it to comply with ANSI SQL. The same statement could be written in Microsoft SQL Server like this:

select %CurrentDateOut;

In Oracle Database, as:

select %CurrentDateOut
from dual;

In both cases, the sentences are a better performing option. Both solutions do not require accessing any physical table.

The problem with these solutions is that they are platform specific, and we want to avoid platform specific syntax. Believe me, when you perform a platform migration you suddenly have very present in your mind the ancestors of the programmers who used this type of syntax. So, up to now, we had to stick with the SELECT ... FROM PS_INSTALLATION solution.








Until now. PeopleTools 8.54 introduces a new MetaSQL name %SelectDummyTable, which automatically translates into a platform specific sentences. Our previous sample would be written as:

select %CurrentDateOut
from %SelectDummyTable

We now have a platform independent and well performing solution. What else can we ask for? ;-)

Note: I've checked the online PeopleBooks from Oracle and at this point there is no documentation on this Meta SQL. Still, I've conducted some tests and it seems to be working correctly.

1 comment:

  1. Very useful indeed.
    Online PBooks :
    http://docs.oracle.com/cd/E58500_01/pt854pbh1/eng/pt/tpcl/langref_Meta-SQLReference-073bd4.html#u64cb3577-db4f-4efd-a038-3b1b9940442e

    Nicolas.

    ReplyDelete