Rowset Fill Method

 Simple Fill Method 

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.

Local Rowset &GetNbr = CreateRowset(Record.IOBM_PHN_NO_WRK);
&GetNbr.Fill();

   For &i = 1 To &GetNbr.ActiveRowCount
     
     
      &getPhone = &GetNbr.GetRow(&i).GetRecord(Record.IOBM_PHN_NO_WRK).GetField(Field.PHONE).Value;
      &getemplid = &GetNbr.GetRow(&i).GetRecord(Record.IOBM_PHN_NO_WRK).GetField(Field.EMPLID).Value;

End-for ;

---------------------------------------------------------------------------------------------------------------------------
 1.  Using JOIN Keyword.

Some times the criteria for selection comes from a record other than the one your selecting from.

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 Record for the data and JOBCODE_TBL record  for the criteria.


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


----------------------------------------------------------------------------------------------------------------------


2. Using Exist Keyword 



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



------------------------------------------------------------------------------------------------------------------------- 

3 . Using UNION Keyword.
Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM) FROM PS_PERSON WHERE EMPLID = :1", &emplid);