Meta SQL

Resolve Meta-SQL

Meta-SQL is really handy, it lets you write database independent SQL and there are some really nifty Meta-SQL functions for handling joins, effective dates and the other PeopleSoft nuances.

But what do you do when you want to grab some Meta-SQL and put it into your favourite query tool? Do you have to go through and adjust the SQL accordingly? Nope, its as easy as using the Resolve Meta SQL function in application designer.

Simply right click in the SQL editor and click on Resolve Meta SQL and watch as the SQL is generated for you in the Meta SQL window.

Here's an screenshot with the HR view, DEPT_SEC_SRCH:

resolve-meta-sql.png

%EffdtCheck

There is a very handy meta-sql element called %EffdtCheck which writes effective date logic for you. For example, I'm writing a view that returns the maximum effective dated and active subject from the subject table.

Here's the meta-SQL:

SELECT INSTITUTION 
, SUBJECT 
, DESCR 
, ACAD_ORG 
, DESCRFORMAL 
FROM PS_SUBJECT_TBL SUBJ 
WHERE %EffdtCheck(SUBJECT_TBL SUBJ_ED, SUBJ, %CurrentDateIn) 
AND SUBJ.EFF_STATUS = 'A'

The line:

%EffdtCheck(SUBJECT_TBL SUBJ_ED, SUBJ, %CurrentDateIn)

Expands to get the maximum effective date from the subject table joining on the keys where the effective date is less than or equal to %CurrentDateIn (or whatever date you decide). Note that SUBJ_ED is the alias to the table used for the effective dated join. The second parameter, SUBJis the alias of the root table.

%EffdtDtCheck doesn't include effective sequence or effective status logic so you'll still have to write that the old fashioned way. To check your sql, use the resolve meta-sql functionality in application designer.

So why bother doing this?

  1. If the structure of the base table changes and there is a new key, the Meta-SQL will automatically adjust to include the new key. This means you don't have to update the view SQL.
  2. It reduces the possiblity of missing out joins on keys when you write your effective date logic.
If you use this with the Rowset Fill method, the table alias (parameter 2) is just FILL. This is the alias given automatically PeopleSoft where expanding the SQL in the Fill statement.

Limitations

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause. However, it cannot be used with records that contain EFFSEQ as part of the key - in that case, the %EffDtCheck construct is expanded into an effective date subquery INCLUDING the EFFSEQ in the join criteria - which is wrong!

For example, the following SQL:

SELECT *
FROM PS_JOB JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND %EffdtCheck(JOB, JOB_ES, %CurrentDateIn)

Expands into:

SELECT *
FROM PS_JOB JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND JOB_ES.EFFDT=(
  SELECT MAX(EFFDT)
  FROM PS_JOB JOB
  WHERE JOB.EMPLID=JOB_ES.EMPLID
  AND JOB.EMPL_RCD=JOB_ES.EMPL_RCD
  AND JOB.EFFSEQ=JOB_ES.EFFSEQ /* this shouldn't be used in EFFDT sub-query */
  AND JOB.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
)

As such, when retrieving the current effective row for a record with EFFSEQ as part of the key, the %EffDtCheck construct should not be used. This also applies to other records that contain EFFDT field + additional fields after EFFDT that indicate a parent-child relationship.

As shown above, %EffDtCheck uses all keys when it's expanded - which may not be what you want in all cases.

%TextIn

%TextIn is a Meta-SQL construct which is used for inserting data into PeopleSoft Long Character type fields. You will need to use this when long text strings into a Long Character field in certain database platforms. PeopleBooks suggests it is necessary for Sybase and Informix. I have found it is also necessary for Oracle.

For example, to insert into PSMSGCATDEFN table, the field DESCRLONG is Long Character field. You could use Meta-SQL like this:

INSERT INTO PSMSGCATDEFN 
  VALUES (:1 
  , :2 
  , :3 
  , :4 
  , %CurrentDateTimeIn 
  , %TextIn(:5))

This ensures the long character data will be inserted into the DESCRLONG field. Without the %TextIn around the bind variable, you will get errors like this in your TraceSQL log:

8015 - Bind value is too long

Regex for Meta-SQL Conversion

I've come up with the following regular expressions to add or remove %Table() meta-sql construct. Applications might involve using it to replace standard table names with the %Table() Meta-SQL directive before putting them into the Application Designer SQL Editor or using it to remove the %Table() Meta-SQL directive if you don't have the option to resolve meta-sql. It's also just a good example of regex to match the start and end of a qualifier like brackets and replace them with something else.

Here are the four scenarios that you might have:

Regular PeopleSoft Tables

This is for anything that uses PS_ in your database as a prefix:

Scenario 1: Find something like PS_INSTALLATION and replace with %Table(INSTALLATION)

Find Regex: PS_([^\s]*)
Replace Regex: %Table($1)

View Example in RegExr.

Scenario 2: Find something like %Table(INSTALLATION) and replace with PS_INSTALLATION

Find Regex: %Table\(((?!PS)[^\s]*)\)
Replace Regex: PS_$1

View Example in RegExr.

PeopleTools Tables

This is specifically for PeopleTools tables that always start with PS and do not use the PS_ prefix for the database table name.

Scenario 3: Find something like PSOPRDEFN and change to %Table(PSOPRDEFN)

Find Regex: PS((?!_)[^\s]*)
Replace Regex: %Table(PS$1)

View Example in RegExr.

Scenario 4: Find something like %Table(PSOPRDEFN) and change to PSOPRDEFN

Find Regex: %Table\(((PS)[^\s]*)\)
Replace Regex: $1

View Example in RegExr.

Try it out in your text editor with regular expressions enabled. You may need to put qualifiers around the find regex, e.g. /%Table\(((PS)[^\s]*)\)/g depending on the regex engine you are using.

%Coalesce Meta-SQL

If you're like me, you might first wonder what the word Coalesce even means? Well in terms of SQL constructs, it refers dealing with NULL values. If you're an Oracle user, you might be more familiar with the nvl operation.

A common use for coalesce/nvl is to substitute a default value when a NULL is encountered. Probably the most common example is to substitute a NULL with a 0 on fields you are using for calculations.

So for example:

select QTY * PRICE from YOUR_TABLE;

Works fine unless you have a scenario where either the quantity or price is NULL. Yes that may be weird but this is data we are talking about. Also think what would happen if you were doing a division operation instead of multiplication.

Here's how to write the same statement with coalese using Meta-SQL:

select %Coalesce(QTY, 0) * %Coalesce(PRICE, 0)
from YOUR_TABLE;

Simple, on resolving the Meta-SQL it will be turned into the appropriate syntax for your database platform. If you use Oracle you might wonder why you would bother when you can just use nvl? Well like a lot of other Meta-SQL functions, the benefit is database platform independence. Your code will work on any database supported by PeopleSoft. Up to you.

Date and Time in Meta-SQL

The Meta-SQL date/time constructs you should know about are:

For input/output of the current date/time:

For input/output of any date/time:

Remember that:

NOTE: with PeopleCode if you are using a date that is returned from SQL and storing it into a date variable, make sure you wrap your date with %DateOut in your SQL, otherwise PeopleSoft will throw an invalid date error.

For input/output of a null date/time:

For getting part of a date/time:

For date arithmetic:

%SelectAll

If you ever need to create a view that selects all fields from a particular record, then you should be using the %SelectAll meta-sql construct.

Why? Because %SelectAll uses the underlying record definition to select the fields - so it will always return all fields from the underlying record, even if that record definition changes.

The basic syntax is:

%SelectAll(RECORD_NAME ALIAS)

There is all a %SelectDistinct construct which adds a distinct to the select clause and uses the same syntax.

%SelectAll returns all the fields for the record specified and includes a from clause.

For example, say I want the latest effective dated, active fields from PSXLATITEM. My SQL might start something like this:

select
    FIELDNAME, 
    FIELDVALUE, 
    EFFDT, 
    EFF_STATUS, 
    XLATLONGNAME, 
    XLATSHORTNAME, 
    LASTUPDDTTM, 
    LASTUPDOPRID, 
    SYNCID  
from 
    PSXLATITEM A  
where 
    EFFDT = (  
        select max(EFFDT)
        from PSXLATITEM
        where FIELDNAME = A.FIELDNAME  
        and FIELDVALUE = A.FIELDVALUE  
        and A.EFFDT <= sysdate
    )
    and A.EFF_STATUS = 'A'
;

Instead of typing out all those fields, lets use %SelectAll (that's what I did to generate this example by the way).

So our meta-sql would look like this - (I've also replaced sysdate with %CurrentDateIn)

%SelectAll(PSXLATITEM A)
where 
    EFFDT = (  
        select max(EFFDT)
        from PSXLATITEM
        where FIELDNAME = A.FIELDNAME  
        and FIELDVALUE = A.FIELDVALUE  
        and A.EFFDT <= %CurrentDateIn
    )
    and A.EFF_STATUS = 'A'
Note that %SelectAll wraps date, time and date/time fields with %DateOut, %TimeOut, %DateTimeOut as well.

This resolves into the following Meta-SQL:

SELECT A.FIELDNAME 
 , A.FIELDVALUE 
 , TO_CHAR(A.EFFDT 
 ,'YYYY-MM-DD') 
 , A.EFF_STATUS 
 , A.XLATLONGNAME 
 , A.XLATSHORTNAME 
 , TO_CHAR(A.LASTUPDDTTM 
 ,'YYYY-MM-DD-HH24.MI.SS."000000"') 
 , A.LASTUPDOPRID 
 , A.SYNCID  
  FROM PSXLATITEM A   
 WHERE EFFDT = (  
 SELECT MAX(EFFDT)  
  FROM PSXLATITEM  
 WHERE FIELDNAME = A.FIELDNAME  
   AND FIELDVALUE = A.FIELDVALUE  
   AND A.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )  
   AND A.EFF_STATUS = 'A'
Note that the example includes an alias of A in the parameters to %SelectAll. If you are using %SelectAll you might have to play with your SQL a bit to get it work, especially if you are using joins to other tables. It may not work in all cases, but if nothing else, its a time saver!

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:

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:

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 are some cases where you should always try to incorporate Meta-SQL: