Standalone Rowset’s Fill Method

A standalone rowset can be populated with rows directly from the database by using the Fill method. It’s quite common to see where strings in the Fill method to reference the FILL correlation ID inside subqueries to limit the resultset.

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

 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}

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

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.