FETCH(basedataset, index, position [, transform ] [, LOCAL] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )
| basedataset | The base DATASET attribute to process. Filtering is not allowed. |
| index | The INDEX attribute that provides keyed access into the basedataset. This will typically have a filter expression. |
| position | An expression that provides the means of locating the correct record in the basedataset (usually the field within the index containing the fileposition value). |
| transform | The TRANSFORM function to call for each record fetched from the basedataset. If omitted, FETCH returns a set containing all fields from both the basedataset and index, with the second of any duplicate named fields removed. |
| LOCAL | Optional. Specifies the operation is performed on each supercomputer node independently, without requiring interaction with all other nodes to acquire data; the operation maintains the distribution of any previous DISTRIBUTE. |
| UNORDERED | Optional. Specifies the output record order is not significant. |
| ORDERED | Specifies the significance of the output record order. |
| bool | When False, specifies the output record order is not significant. When True, specifies the default output record order. |
| STABLE | Optional. Specifies the input record order is significant. |
| UNSTABLE | Optional. Specifies the input record order is not significant. |
| PARALLEL | Optional. Try to evaluate this activity in parallel. |
| numthreads | Optional. Try to evaluate this activity using numthreads threads. |
| ALGORITHM | Optional. Override the algorithm used for this activity. |
| name | The algorithm to use for this activity. Must be from the list of supported algorithms for the SORT function's STABLE and UNSTABLE options. |
| Return: | FETCH returns a record set. |
The FETCH function processes through all records in the index in the order specified by the index, fetching each related record from the basedataset and performing the transform function.
The index will typically have a filter expression to specify the exact set of records to return from the basedataset. If the filter expression defines a single record in the basedataset, FETCH will return just that one record. See KEYED/WILD for a discussion of INDEX filtering.
The transform function must take up to two parameters: a LEFT record that must be of the same format as the basedataset, and an optional RIGHT record that that must be of the same format as the index. The optional second parameter is useful in those instances where the index contains information not present in the recordset.
Example:
PtblRec := RECORD
STRING2 State := Person.per_st;
STRING20 City := Person.per_full_city;
STRING25 Lname := Person.per_last_name;
STRING15 Fname := Person.per_first_name;
END;
PtblOut := OUTPUT(TABLE( Person,PtblRec),,'RTTEMP::TestFetch');
Ptbl := DATASET('RTTEMP::TestFetch',
{PtblRec,UNSIGNED8 __fpos {VIRTUAL(fileposition)}},
FLAT);
Bld := BUILD(Ptbl,
{state,city,lname,fname,__fpos},
'RTTEMPkey::TestFetch');
AlphaInStateCity := INDEX(Ptbl,
{state,city,lname,fname,__fpos},
'RTTEMPkey::TestFetch');
TYPEOF(Ptbl) copy(Ptbl l) := TRANSFORM
SELF := l;
END;
AlphaPeople := FETCH(Ptbl,
AlphaInStateCity(state='FL',
city ='BOCA RATON',
Lname='WIK',
Fname='PICHA'),
RIGHT.__fpos,
copy(LEFT));
OutFile := OUTPUT(CHOOSEN(AlphaPeople,10));
SEQUENTIAL(PtblOut,Bld,OutFile)
//NOTE the use of a filter on the index file. This is an important
// use of standard filtering technique in conjunction with indexing
// to achieve optimal "random" access into the base record set
See Also: TRANSFORM Structure, RECORD Structure, BUILDINDEX, INDEX, KEYED/WILD