Meta-SQL

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:

Table Description Key Fields
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
    )  
;

Note that the SEQNUM field is used to partition long SQL statements. If you have a SQLID with a SEQNUM greater than 0, it is because it is too long to fit in one row for the SQLTEXT long field. Since you would want to return all SEQNUM rows, there is no need to add any conditions for SEQNUM in the query above.

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.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License