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

 ) 

;

 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.

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.

 NOTE: 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.

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