Extra Hot

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.