- Resolve Meta-SQL
- Regex for Meta-SQL Conversion
- %Coalesce Meta-SQL
- Date and Time in Meta-SQL
- Writing 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,
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'
%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?
- 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.
- It reduces the possiblity of missing out joins on keys when you write your effective date logic.
%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)
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.
%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
Find Regex: PS_([^\s]*) Replace Regex: %Table($1)
View Example in RegExr.
Scenario 2: Find something like
%Table(INSTALLATION) and replace with
Find Regex: %Table\(((?!PS)[^\s]*)\) Replace Regex: PS_$1
View Example in RegExr.
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
Find Regex: PS((?!_)[^\s]*) Replace Regex: %Table(PS$1)
View Example in RegExr.
Scenario 4: Find something like
%Table(PSOPRDEFN) and change to
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.
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
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:
- input date/time goes in the
- output date/time goes in the
SELECTclause (returned data)
For input/output of a null date/time:
For getting part of a date/time:
%DatePartgets the date
%TimePartgets the time
For date arithmetic:
%DateAdd(from, days_to_add)gives the resulting date. You can use negative numbers to get a past date. So this is also the DateSubtract function.
%DateDiff(from, to)gives the difference between two dates in days
%DateTimeDiff(from, to)gives the difference between two date/times in minutes
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.
%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:
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
%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'
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'
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
PSSQLTEXTDEFN- SQL Definitions PeopleTools record for stored SQL statements text with key fields
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
%Tableand the PeopleSoft record definition names
%Jointo perform the join on common keys between
- Replacing the effective date logic with
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
%Joinso 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
- Joins so that you are always using the correct keys
- Standard effective date logic