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", &paramGrade);

Now you’ve got SQL that’s easier to write and easier to maintain than a WHERE EXISTS clause.



Post a Comment:
Comments are closed for this entry.