Meta-SQL is a great way to abstract SQL logic and ensure consistency in your SQL definitions. It isn't just for database platform independence, it is actually a better way to write SQL for PeopleSoft.
This article provides an example of how to write Meta-SQL starting with standard SQL. In the example, the following tables are used to return information about stored PeopleTools SQL definitions:
PSSQLDEFN- SQL Definitions PeopleTools record with key fields
PSSQLTEXTDEFN- SQL Definitions PeopleTools record for stored SQL statements text with key fields
Firt here's the standard SQL query get all SQL statements from the PeopleTools database with the latest effective dated unique rows:
select S.SQLID, S.SQLTYPE, S.VERSION, S.LASTUPDOPRID, S.LASTUPDDTTM, S.ENABLEEFFDT, S.OBJECTOWNERID, ST.MARKET, ST.DBTYPE, ST.EFFDT, ST.SEQNUM, ST.SQLTEXT from PSSQLDEFN S, PSSQLTEXTDEFN ST where ST.SQLID = S.SQLID and ST.SQLTYPE = S.SQLTYPE and ST.EFFDT = ( select max(EFFDT) from PSSQLTEXTDEFN where SQLID = ST.SQLID and SQLTYPE = ST.SQLTYPE and MARKET = ST.MARKET and DBTYPE = ST.DBTYPE and SEQNUM = ST.SEQNUM and EFFDT <= sysdate ) ;
We can refactor this SQL statement with the following steps:
- Replacing the hard coded database table names with
%Tableand the PeopleSoft record definition names
%Jointo perform the join on common keys between
- Replacing the effective date logic with
Here's how it looks after this refactoring:
SELECT S.SQLID , S.SQLTYPE , S.VERSION , S.LASTUPDOPRID , S.LASTUPDDTTM , S.ENABLEEFFDT , S.OBJECTOWNERID , ST.MARKET , ST.DBTYPE , ST.EFFDT , ST.SEQNUM , ST.SQLTEXT FROM %Table(PSSQLDEFN) S , %Table(PSSQLTEXTDEFN) ST WHERE %Join(COMMON_KEYS, PSSQLDEFN S, PSSQLTEXTDEFN ST) AND %EffdtCheck(PSSQLTEXTDEFN ST_ED, ST, %CurrentDateTimeIn)
To verify you can use resolve meta-sql to get the original SQL back.
While Meta-SQL is definitely good practice, there are some caveats to be mindful of:
- There may be cases where using meta-sql constructs lead to poor performance, in such cases you should stick to regular SQL (and also investigate why standard key joins are not sufficient)
- Meta-SQL doesn't support the ANSI SQL join syntax (which is much neater) but it does abstract away the join using
%Joinso this is really isn't something you see unless you resolve meta-sql to debug.
- Meta-SQL doesn't work correctly if you have effective date logic with effective sequence so always double check this logic.
There are some cases where you should always try to incorporate Meta-SQL:
- When you need date and time functions
- When referencing record names use
- Joins so that you are always using the correct keys
- Standard effective date logic