Writing Meta-SQL

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 SQLIDand SQLTYPE
• PSSQLTEXTDEFN - SQL Definitions PeopleTools record for stored SQL statements text with key fields SQLID, SQLTYPE, MARKET, DBTYPE, EFFDT and SEQNUM

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 %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 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 %Join so 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 %Table
• Joins so that you are always using the correct keys
• Standard effective date logic