Category Archives: PeopleSoft

A Look at the Standalone Rowset’s Fill Method

Posted on by
Reply

A standalone rowset can be populated with rows directly from the database by using the Fill method. PeopleBooks describes the syntax of the Fill method as follows:

Fill([wherestring [, bindvalue] . . .]);

Data is read from the database table corresponding to the primary database record of the rowset. It’s also stated in PeopleBooks that:

The Fill method uses a correlation ID of FILL for the table it reads. You must use the correlation ID if you want to refer to the rowset table name as part of the wherestring. You receive a runtime error if you use the table name as a column prefix instead of the correlation ID.

It’s quite common to see wherestrings in the Fill method to reference the FILL correlation ID inside subqueries to limit the resultset. For example, something like the following:


Local Rowset &rs = CreateRowset(Record.RF_INST_PROD);

&rs.Fill("WHERE RBTACCTID = :1 AND EXISTS (SELECT 1 FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);

or


&rs.Fill("WHERE RBTACCTID = :1 AND INST_PROD_ID IN (SELECT INST_PROD_ID FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);

However, if you observe it closely (ignoring related-language processing), the Fill method would basically just generate a SQL statement in the following manner:

SELECT {list of fields from primary record} FROM %TABLE({primary record}) FILL {wherestring}

and push the resultset to the target rowset.

With this in mind, it’s possible to pull data from a join query instead. For example, the previous Fill call can be rewritten as:


&rs.Fill("INNER JOIN PS_RF_INST_PROD_ST ST ON ST.SETID = FILL.SETID AND ST.INST_PROD_ID = FILL.INST_PROD_ID WHERE FILL.RBTACCTID = :1 AND ST.INST_PROD_STATUS = 'INS')", &acctid);

Interestingly, it also means you could fill a rowset with data not coming from the primary record by using a UNION. For example (notice the fields doesn’t even have to match the primary record):


Local Rowset &rs = CreateRowset(Record.PSOPRCLS); /* Fields: OPRID, OPRCLASS */

&rs.Fill("WHERE 1=0 UNION SELECT OPRID, ROWSECCLASS FROM PS_ABS_DEPT2_VW WHERE EMPLID = :1", &emplid);

This latter example is more for academic discussion, as I can’t vouch for the database efficiency in using this technique. Also, when the fields of the primary record is updated, there is a need to manually adjust the code to match the primary record definition. However, it might be handy for quick prototypes and filling message rowsets with data directly without the need for new SQL Views and CopyTo operation.

Postscript: A quick googling shows that this concept has already been presented by Todd Kummer here.

Using MsgGetText to Concatenate Strings

Posted on by
3

Lately, I’ve been using the MsgGetText() built-in function to concatenate adhoc strings in PeopleCode. For example, instead of the following:


Local string &TEXTLINE = "Value of " | &fld1name | " is " | &fld1value | ".";

Using MsgGetText, it can be re-written as follows:


Local string &TEXTLINE = MsgGetText(0, 0, "Value of %1 is %2.", &fld1name, &fld1value);

I find that the latter is easier to read and modify compared to the first, especially if there are a lot of values being concatenated.

What about performance? Wouldn’t the MsgGetText code needlessly query the database for message (0, 0)? Fortunately, I think not. Some tests with SQL trace turned on — and the cache freshly cleared — show that a query to the message catalog for message (0, 0) is never performed by the application server. A wonderful optimization.

Set Processing 101

Posted on by
1

When I was reading the What’s New with Batch Processing in PeopleSoft Enterprise Oracle OpenWorld presentation some time ago, the following slide caught my attention:

What are the Problems with AppEngine

  • These programs can be complicated – and for a programmer they’re different
  • Same logic can be used Batch and Online – Really?
    • Different transactional model
    • Temp table behavior is very different
    • No restartability
  • Easy to write a poor performing App Engine Program
    • Since the introduction of PeopleCode, poor performing programs are the norm

 

Admittedly, I don’t have a lot of experience writing batch programs on Application Engine. But I believe the primary reason it is so easy to write poor-performing AE programs is that AE batch programs are better suited to be programmed using set processing. Set processing requires a different mind set to what procedural programmers are used to. I am new to it as well, so I find that this discussion in ITToolbox, and this followup provides valuable insights on how to perform set processing. Steve’s technique of what he calls partial cartesian joins is quite useful as well, especially for those not running an Oracle database.

PeopleSoft Search

Posted on by
3

If you’ve been browsing my blog directly, you may have already noticed the PeopleSoft Search link on the site’s header. This is simply a Google Custom search engine pointing to sites containing relevant PeopleSoft technical content. This includes mostly forums and blogs.

I find this useful when I need to search for keywords that are not specifically related to PeopleSoft. Following is an example result when searching for “excel”:
Sample Search result

I still have trouble filtering out RSS from the results, if anyone can help me with that.

Now, if you’re browsing my site using Firefox 2, you may also notice the following auto-discovered PeopleSoft Search item on your search bar:
Firefox Search Engines

Following is how the auto-discovered search would like it IE7:
IE7 Search Engines

This plugin is packaged using the OpenSearch standard, as described in this Mozilla article.

The plugin can also be installed by clicking the following link:

Install PeopleSoft Search

PeopleCode Array class’ generic sort?

Posted on by
3

I’ve come across the following statement on PeopleBooks (PeopleCode API Reference > Application Classes > When Would You Use Application Classes?). This statement can be found in PeopleBooks for PeopleTools versions 8.45 to 8.49:

… suppose you want to provide a more generic sort, with comparison function at the end of it. You want to use the array class Sort method, but your process has to be generic: you aren’t certain if you’re comparing two records or two strings. You could define a class that had as its main method a comparison, and return a -1, 0, or 1. Then write your personalized sort, extending the array class method Sort.

I find this statement very vague and lacking in details, that I am doubtful about its accuracy.

The statement seems to somewhat describe how one would define the ordering of objects in Java’s array and collection classes by creating an implementation of java.util.Comparator interface. However, according to PeopleBooks, the Sort method of the Array class does not have any optional parameters where such a comparator object may be provided.

I couldn’t find anything else in PeopleBooks where it describes the implementation of array sorting in the manner described in the statement above. Can anyone shed some light into this, and if possible, provide additional details? Does the PeopleCode Array class Sort method have an undocumented feature similar Java Arrays’ sort method?