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:

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)

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 S.SQLID = ST.SQLID
   AND S.SQLTYPE = ST.SQLTYPE
   AND ST.EFFDT=(
 SELECT MAX(EFFDT)
  FROM PSSQLTEXTDEFN ST_ED
 WHERE ST_ED.SQLID=ST.SQLID
   AND ST_ED.SQLTYPE=ST.SQLTYPE
   AND ST_ED.MARKET=ST.MARKET
   AND ST_ED.DBTYPE=ST.DBTYPE
   AND ST_ED.SEQNUM=ST.SEQNUM
   AND ST_ED.EFFDT<=SYSDATE)

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.