The ETL (Extract, Transform, and Load--standard data ingest processing) operations in ECL typically operate against all or most of the records in any given dataset, which makes the use of keys (INDEX files) of little use. Many queries do the same.
However, production data delivery to end-users rarely requires accessing all records in a dataset. End-users always want "instant" access to the data they're interested in, and most often that data is a very small subset of the total set of records available. Therefore, using keys (INDEXes) becomes a requirement.
The following attribute definitions used by the code examples in this article are declared in the DeclareData MODULE structure attribute in the DeclareData.ECL file:
EXPORT Person := MODULE
EXPORT File := DATASET('~PROGGUIDE::EXAMPLEDATA::People',Layout_Person, THOR);
EXPORT FilePlus := DATASET('~PROGGUIDE::EXAMPLEDATA::People',
{Layout_Person,
UNSIGNED8 RecPos{VIRTUAL(fileposition)}}, THOR);
END;
EXPORT Accounts := DATASET('~PROGGUIDE::EXAMPLEDATA::Accounts',
{Layout_Accounts_Link,
UNSIGNED8 RecPos{VIRTUAL(fileposition)}}, THOR);
EXPORT PersonAccounts := DATASET('~PROGGUIDE::EXAMPLEDATA::PeopleAccts',
{Layout_Combined,
UNSIGNED8 RecPos{virtual(fileposition)}},THOR);
EXPORT IDX_Person_PersonID := INDEX(Person.FilePlus,{PersonID,RecPos},
'~PROGGUIDE::EXAMPLEDATA::KEYS::People.PersonID');
EXPORT IDX_Accounts_PersonID := INDEX(Accounts,{PersonID,RecPos},
'~PROGGUIDE::EXAMPLEDATA::KEYS::Accounts.PersonID');
EXPORT IDX_Accounts_PersonID_Payload :=
INDEX(Accounts,
{PersonID},
{Account,OpenDate,IndustryCode,AcctType,
AcctRate,Code1,Code2,HighCredit,Balance,RecPos},
'~PROGGUIDE::EXAMPLEDATA::KEYS::Accounts.PersonID.Payload');
EXPORT IDX_PersonAccounts_PersonID :=
INDEX(PersonAccounts,{PersonID,RecPos},
'~PROGGUIDE::EXAMPLEDATA::KEYS::PeopleAccts.PersonID');
EXPORT IDX__Person_LastName_FirstName :=
INDEX(Person.FilePlus,{LastName,FirstName,RecPos},
'~PROGGUIDE::EXAMPLEDATA::KEYS::People.LastName.FirstName');
EXPORT IDX__Person_PersonID_Payload :=
INDEX(Person.FilePlus,{PersonID},
{FirstName,LastName,MiddleInitial,
Gender,Street,City,State,Zip,RecPos},
'~PROGGUIDE::EXAMPLEDATA::KEYS::People.PersonID.Payload');
Although you can use an INDEX as if it were a DATASET, there are only two operations in ECL that directly use keys: FETCH and JOIN.
The FETCH is the simplest use of an INDEX. Its purpose is to retrieve records from a dataset by using an INDEX to directly access only the specified records.
The example code below (contained in the IndexFetch.ECL file) illustrates the usual form:
IMPORT $;
F1 := FETCH($.DeclareData.Person.FilePlus,
$.DeclareData.IDX_Person_PersonID(PersonID=1),
RIGHT.RecPos);
OUTPUT(F1); You will note that the DATASET named as the first parameter has no filter, while the INDEX named as the second parameter does have a filter. This is always the case with FETCH. The purpose of an INDEX in ECL is always to allow "direct" access to individual records in the base dataset, therefore filtering the INDEX is always required to define the exact set of records to retrieve. Given that, filtering the base dataset is unnecessary.
As you can see, there is no TRANSFORM function in this code. For most typical uses of FETCH a transform function is unnecessary, although it is certainly appropriate if the result data requires formatting, as in this example (also contained in the IndexFetch.ECL file):
r := RECORD
STRING FullName;
STRING Address;
STRING CSZ;
END;
r Xform($.DeclareData.Person.FilePlus L) := TRANSFORM
SELF.Fullname := TRIM(L.Firstname) + TRIM(' ' + L.MiddleInitial) + ' ' + L.Lastname;
SELF.Address := L.Street;
SELF.CSZ := TRIM(L.City) + ', ' + L.State + ' ' + L.Zip;
END;
F2 := FETCH($.DeclareData.Person.FilePlus,
$.DeclareData.IDX_Person_PersonID(PersonID=1),
RIGHT.RecPos,
Xform(LEFT));
OUTPUT(F2);
Even with a TRANSFORM function, this code is still a very straight-forward "go get me the records, please" operation.