Rowset Fill Method and the JOIN Keyword
The Rowset method Fill is used to populate a stand-alone rowset from the
database. It take a where clause as a parameter. The underlying record
is used as a select record. Here’s the description from PeopleBooks:
The Fill method flushes the rowset then reads records from the database into successive rows. The records are read from the database tables corresponding to the primary database record of the scroll into that record. The records are selected by the optional wherestring SQL clause, in which the optional bindvalues are substituted, using the usual bind placeholders (:n).
Often
times the criteria for selection comes from a record other than the one
your selecting from. Up until this point I’ve been writing WHERE EXISTS
clauses to check that type of criteria. However, it turns out that you
can have more than one record in the statement by use of the JOIN keyword. The component processor generates the SELECT
automatically based upon the fields in the record and the record name.
After that, it just appends the where clause passed into the method.
Since that’s the case, there’s no reason that where clause has to start
out with WHERE. For example, let’s say you want to select records from the JOB table, but only those with a Job Code in a certain Grade. Basically, you need JOB for the data and JOBCODE_TBL for the criteria. Here’s what that PeopleCode might look like (minus the effdt considerations):
Local Record &job = CreateRecord(Record.JOB);
&job.Fill("JOIN PS_JOBCODE_TBL JC ON FILL.SETID = JC.SETID
AND FILL.JOBCODE = JC.JOBCODE WHERE JC.GRADE = :1", ¶mGrade);
Now you’ve got SQL that’s easier to write and easier to maintain than a WHERE EXISTS clause.