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.

Adding new fields... to an existing record or a new one?

At the very start of every PeopleSoft project we're confronted to the task of defining the Development Standards. One of the recurring discussion points is what to do when we need a new field in a record.

a) Create a sibling record (same primary key), adding the new field as an attribute to that record.

b) Add the new field in the original record.

At first, the a) option seems the cleanest one as it does not modify standard records and it sounds like it will easier to maintain through upgrades and fixes.

However, this option also has its limitations:

• Querying the table now would involve joining two tables. This leads to additional complexity and performance issues (particularly if customizations have been made by adding sibling records to multiple records in a query).
• When creating a query, you may need to filter on both the standard record and the sibling one. In that case, you won't be able to use an index on both filters, as the fields are placed in different tables.
• Adding a sibling records means you need to use an additional scroll area in components showing both the original record and the standard one. In some situations, this may put in a situation where the 4 allowed scroll levels (0, and 1 to 3) are not enough.
  

Mainly due to performance reasons, my preferred option is b). In order to keep a clearer track for upgrades and fixes, what we normally do is to place new fields in a subrecord defined explicitly for customized fields. In such way, it is always easy to identify the customized fields in a record.

What is your experience on record customization? Which of the two options you prefer?