Synergex.SynergyDE.Select.Rows

Access record sets returned by each Join iteration

namespace Synergex.SynergyDE.Select
public class Rows

The Rows class contains methods and properties to access record sets that are returned by each iteration of a join. A Rows object is the result returned from a Join enumeration, in the same way that a record is the result of a Select enumeration, and it contains the current (most recently joined) record set.

Properties

Indexer

public Indexer(index)

Returns a record set using the index (int) from a call to JoinSelect.GetRowArrayIndex() as a boxed alpha. (@a)

TotalReadCount

public TotalReadCount

Implements a get method that returns the total number of READ operations made to retrieve all record sets up to the current record set. (int)

CurrentReadCount

public CurrentReadCount

Implements a get method that returns the number of READ operations made to retrieve the current record set. (int)

Methods

Fill

public Fill(record), boolean

Loads the specified record (a) with a row from the most recent RowEnumerator iteration and identifies if the record was matched. All fields are loaded, or if Select.Sparse is used, the fields specified by the Sparse object are loaded and all others are cleared. Record must be the exact same record specified in one of the From objects used in the Join; otherwise an INVARG error (“Invalid argument”) will be generated.

Rows.Fill returns true if the specified row was matched or false if the row doesn’t match the join criteria (equivalent to a SQL NULL value during an outer join). A specified row that results in not matching a left join criteria is entirely cleared, and false is returned.

RowInfo

public RowInfo(record, [rfa], [reclen][, isChanged]), boolean 
public RowInfo(index, [rfa], [reclen][, isChanged]), boolean

RowInfo(record,...) loads metadata for the specified record from the record set returned from the most recent iteration.

RowInfo(index,...) loads metadata for the record specified by the Rows index from the record set returned from the most recent iteration.

record

The record to load metadata for. (a)

rfa

(optional) Returned with the RFA location of the record. (a6 or a10)

reclen

(optional) Returned with the record length. (n)

isChanged

(optional) Returns true if the Rows contents of the specified field have changed or false if the exact same record was returned. (boolean)

index

The record (by JoinSelect.GetRowArrayIndex()) to load metadata for. (n)

If the specified record was matched, both RowInfo() methods will return true, and all specified fields will be loaded with the appropriate metadata. If the specified record was not matched, both methods will return false, and all specified metadata fields will be cleared.

Discussion

Here are some things you can do with the Rows.Fill() and Rows.RowInfo() methods.

Reload the order record only when it changes.

foreach rows in joinObj
  begin
    rows.RowInfo(order, , , orderChanged)
    if (orderChanged)
      rows.fill(order)
    rows.fill(orderdetail)
       .
       .
       .
  end

Avoid redisplaying the customer record for each order, using RowInfo() to recognize when a customer record has changed as well as if the customer made an order.

begin
    rows.RowInfo(customer,,, newRow)
    if (newRow)           ; Display the Customer information once
      begin
        rows.fill(customer)
        xcall ShowCustomer(customer)
      end
    if (rows.RowInfo(order,,)) then
      begin               ; Display each Order made by a customer
        rows.fill(order)
        xcall ShowOrder(order)
      end
    else
      xcall ShowNoOrder()
end

Get additional information from certain returned rows.

wasJoined = rows.RowInfo(order, rfa, reclen, orderChanged)

Process rows in an external subroutine where the From records are not available by using the Rows indexer. The indices are static so they can be initialized outside the enumeration. The rows[ix] indexer returns a boxed alpha so it can be used directly without the original record supplied in the From object.

idx1 = joinObj.GetRowArrayIndex(order)
    idx2 = joinObj.GetRowArrayIndex(orderDetail)
    idx3 = joinObj.GetRowArrayIndex(customer)
    idx4 = joinObj.GetRowArrayIndex(product)
    foreach rows in joinObj
      begin
        xcall showrows(rows, idx1)
        xcall showrows(rows, idx2)
        xcall showrows(rows, idx3)
        xcall showrows(rows, idx4)

        .products
        .
        .
    end
.
.
.
subroutine showrows
rows ,@Rows
    idx  ,i
record
    myRow ,@A
proc
    writes(15, "Row " + %string(idx) + " = " + %atrim(rows[idx]))
    xreturn
end

TotalReadCount behaves slightly differently with static cursors. For a single-table query, the initial TotalReadCount when an enumerator is created reflects the total number of reads required to add all entries in the cursor (which might not be equal to the number of records in the result set, depending on whether the records were read individually or in blocks). For each subsequent iteration over the result set, CurrentReadCount will be 1, and this value will be added to TotalReadCount. For joins, TotalReadCount behaves similarly, although CurrentReadCount will typically be 1 per table in the join. (The actual value may vary based on whether a record is different between subsequent rows.)