A Select object after a Join method is applied
|
WSupported on Windows
|
USupported on Unix
|
VSupported on OpenVMS
|
NSupported in Synergy .NET
|
namespace Synergex.SynergyDE.Select public sealed class JoinSelect
The JoinSelect class object is required when using Joins. The Join() method in the Select class converts a Select object into a JoinSelect object. (Note that a Join operation must be included somewhere in the Select.)
public GetEnumerator(), @RowEnumerator
Creates and returns a RowEnumerator object. See Synergex.SynergyDE.Select.RowEnumerator.
public GetRowArrayIndex(record), n
Returns the index of the specified record (a) in any Rows object created by that RowEnumerator. This index remains static throughout the life of the JoinSelect object. Using GetRowArrayIndex() along with the indexer is an alternative to using the Rows.Fill() method. If the specified rec passed to GetRowArrayIndex() does not exactly match a record specified in the Join, an INVARG error (“Invalid argument”) is generated.
Discussion
No record updates or deletes are available via the JoinSelect object.
The Select becomes a JoinSelect when the Join() method is applied to the Select object. The on parameter is part of the From.InnerJoin() or From.LeftJoin() method.
In the following example, the JoinSelect object starts out like a Select but includes Join(). If there is no InnerJoin() or LeftJoin(), an INVOPER error (“JoinSelect requires one or more From.InnerJoin or From.LeftJoin specifications”) is generated.
joinObj = new Select( fromOrders.InnerJoin( fromOrderdetails, & (On)(order.OrderID == orderdetail.OrderID) ) ).Join()
Sorting results
As with Select, it is possible to use an OrderBy (or NoCaseOrderBy) object to determine the order of rows returned by JoinSelect. The AscendingKey() and DescendingKey() methods will process the selection in ascending or descending order by a key of reference in the first file. The Ascending() and Descending() methods are more powerful, as they allow you to sort results by any field(s) from any file in the Join. If multiple fields are specified in OrderBy.Ascending() or OrderBy.Descending(), priority will be given from left to right.
When sorting results using OrderBy.Ascending() and/or OrderBy.Descending(), JoinSelect uses a static cursor to sort and retrieve results. For large result sets, this may require a large amount of memory. To reduce memory usage and improve performance, we recommend using a Sparse object to limit the returned fields to those that are essential to the query, instead of retrieving full records from each file. To encourage optimization, all queries that use a static cursor require the use of a Sparse object. However, it's possible to specify “new Sparse("*")” if you still want to include all fields.
See also
Examples
import Synergex.SynergyDE.Select
record categories
CategoryID ,d5
CategoryName ,a20
Description ,a100
record products
ProductID ,d5
ProductName ,a50
SupplierID ,d5
CategoryID ,d5
Unit ,a20
Price ,d5.2
record
jobj ,@JoinSelect
rowobj ,@Rows
...
jobj = new Select(new From(1, categories)
& .InnerJoin(new From(2, products),
& (On)(categories.CategoryID == products.CategoryID))).Join()
foreach rowobj in jobj
begin
rowobj.fill(categories) ;Fill categories record
rowobj.fill(products) ;Fill products record
writes(TTCHN, %string(ProductID) + " " + ProductName + " " +
& CategoryName + " " + %string(categories.CategoryID))
end jobj = new Select(new Sparse(categories.CategoryID, CategoryName,
& products.CategoryID, ProductID, ProductName),
& new From(1, categories)
& .InnerJoin(new From(2, products),
& (On)(categories.CategoryID == products.CategoryID)),
& OrderBy.Ascending(categories.CategoryID,ProductID)).Join()
foreach rowobj in jobj
begin
rowobj.fill(categories) ;Fill categories record
rowobj.fill(products) ;Fill products record
writes(TTCHN, %string(ProductID) + " " + ProductName + " " +
& CategoryName + " " + %string(categories.CategoryID))
end
