Skip to main content

FETCH

FETCH(basedataset, index, position [, transform ] [, LOCAL] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

basedatasetThe base DATASET attribute to process. Filtering is not allowed.
indexThe INDEX attribute that provides keyed access into the basedataset. This will typically have a filter expression.
positionAn expression that provides the means of locating the correct record in the basedataset (usually the field within the index containing the fileposition value).
transformThe 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.
LOCALOptional. 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.
UNORDEREDOptional. Specifies the output record order is not significant.
ORDEREDSpecifies the significance of the output record order.
boolWhen False, specifies the output record order is not significant. When True, specifies the default output record order.
STABLEOptional. Specifies the input record order is significant.
UNSTABLEOptional. Specifies the input record order is not significant.
PARALLELOptional. Try to evaluate this activity in parallel.
numthreadsOptional. Try to evaluate this activity using numthreads threads.
ALGORITHMOptional. Override the algorithm used for this activity.
nameThe 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.

FETCH TRANSFORM Function Requirements

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