PeopleTools simply evaluates this meta-SQL as 'DUAL' on Oracle.
In Oracle, DUAL is just a convenience table. You don't need to use it, you can use anything you want. PeopleSoft applications often use PS_INSTALLATION when they needs a single row source in a query. This was another example of platform agnosticism. PS_INSTALLATION is available on every platform and every PeopleSoft installation, DUAL is not.
Instead of coding this (the example is taken from ESPP_REF_REVMAIN.PSHUP.Do When)
You can now code this instead:
%Select(IF_FLAG) SELECT 'X' FROM PS_INSTALLATION WHERE %Bind(ST_SEND_SRC) = 'N'
Which resolves to:
%Select(IF_FLAG) SELECT 'X' FROM %SelectDummyTable WHERE %Bind(ST_SEND_SRC) = 'N'
There are two advantages to using DUAL.
%Select(IF_FLAG) SELECT 'X' FROM DUAL WHERE %Bind(ST_SEND_SRC) = 'N'
- In the database, the Oracle optimizer knows that DUAL is a special one row, one column table. When you use it in queries, it uses this knowledge when generating the execution plan.
- If you used a real table, there was a risk that it could have no rows, or more than one row. Either could cause application problems. In previous versions of Oracle, when it was a real physical table, that problem could also occur with DUAL. However, since Oracle 10g it is just a memory structure in the database, and accessing it involves neither a logical nor a physical read.
ConclusionPeopleSoft have created a Meta-SQL that evaluates as DUAL so that this Oracle optimization is implemented in a PeopleSoft platform generic manner.
I would not bother to go back and change existing code to use this, unless perhaps I was visiting the code anyway, but I would certainly recommend its use going forward.