Dynamic From in SQL called by PeopleCode

This is how you can use a dynamic table name in the from clause of SQL called by PeopleCode.

First, you need create your SQL object. This is an example of the code you would use (in this example this SQL object will be named GET_OPRID_FOR_PERSON).

select OPRID
from %Table(:1)
where EMPLID = :2

The %Table parameter returns the SQL (database) table name for a application designer record name. So if you pass it the record name PERSON it would translate it to PS_PERSON. The record name PSOPRDEFN would be translated to … PSOPRDEFN - why? Because PSOPRDEFN has a non-standard SQL name already set as PSOPRDEFN. See this article on reverse engineering PeopleTools tables for more about this.

The next step is to call your SQL object. In this example, I'm going to use SQLExec but you could also use the relevant methods in the SQL class.

Local string &sOprRecName = "PSOPRDEFN";
Local string &sEmplid = '1234';
Local string &sOprid;
SQLExec(SQL.GET_OPRID_FOR_PERSON, @("Record." | &sOprRecName), &sEmplid, &sOprid);

Note that parameter 1 (:1) is passed as @("Record." | &sOprRecName). What does this mean?

Well first you concatenate Record. with the record name stored in the variable &sOprRecName which gives you the string "Record.PSOPRDEFN". However, you don't want to pass the string but rather the PeopleCode as Record.PSOPRDEFN (note there are no quotes around this text). This is what the @ operator does. It essentially coverts strings with PeopleCode into actual, callable PeopleCode.

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