Dynamic From SQL


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).

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.

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 @ ( at meta operator ) does. It coverts strings with PeopleCode into callable PeopleCode.


Revision #1
Created Wed, Jul 31, 2019 11:13 AM by PeopleSoft Wiki
Updated Wed, Jul 31, 2019 11:18 AM by PeopleSoft Wiki