One of the most useful development shortcuts in PeopleSoft is Meta-SQL.
If you don't know about Meta-SQL then this example of how to refactor standard SQL to Meta-SQL should help you get an idea of what it can do.
The Example - PeopleTools SQL Definition Data
In this example, the following tables are used to return information about stored PeopleTools SQL definitions:
|PSSQLDEFN||SQL Definitions||SQLID, SQLTYPE|
|PSSQLTEXTDEFN||SQL Definition Text - Actual Stored SQL||SQLID, SQLTYPE, MARKET, DBTYPE, EFFDT, SEQNUM|
Here's how you might write a query that joins these records to give you the latest effective dated unique fields from these tables:
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 ) ;
Refactoring with Meta-SQL
The refactoring steps for the above SQL statement include:
- Replacing the hard coded database table names with %Table and the PeopleSoft record definition names
- Using %Join to perform the join on common keys between PSSQLDEFN and PSSQLTEXTDEFN
- Replacing the effective date logic with %EffdtCheck
Here's how the Meta-SQL looks after these changes:
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)
Note that because we are selecting fields from more than one table (and not all the fields), the %SelectAll construct is not going to work in this case.
Thoughts on using Meta-SQL
Meta-SQL isn't a silver bullet. There are legitimate cases when you shouldn't use it because it simply can't do what you want or because it does it poorly (e.g. performance). In terms of aesthetics, Meta-SQL doesn't support ANSL SQL join syntax, however the benefits of using Meta-SQL usually outweigh the aesthetic costs.
There are a few Meta-SQL conventions that you should always try to use:
- The relevant Meta-SQL date and time functions constructs
- The %Table construct with record names instead of database table names. See this article about why you should be doing this. Essentially, like the other Meta-SQL constructs it reduces the impact of changes to the system to your code.
Remember to always resolve your Meta-SQL to verify that it creates the correct SQL statement for your database platform.