ch.joinx()
Description:
Join up a channel and a bin file or an entity table by the foreign key.
Syntax:
|
ch.joinx(C:…,f:K:…,x:F,…;…;…;n) |
Match C,… field in channel ch with key K of the segmentable bin file f to find the corresponding records in f |
|
ch.joinx(C:…,T:K:…,x:F,…;…;…;n) |
Match C,… field in channel ch with key K of the entity table T to find the corresponding records in T |
Note:
The function matches the foreign key fields C,… in channel ch with the key K in bin file f or entity table T, which is ordered by K, to find the corresponding record in f /T, and makes the record’s expression x a new field F and adds it to ch. The value corresponding to a mismatched record will be recorded as null.
This is a function that gets the final result set.
Option:
|
@i |
Discard the records whose foreign key values can’t be matched; represent them as nulls by default; When parameters x:F are absent, only perform the filtering on the channel. |
|
@d |
Delete records matching the foreign key and perform the filtering operation only on channel ch; in this case, parameters x:F are absent. |
|
@q |
Speed up the matching action according to a certain order when the channel contains a relatively small amount of data or it is a sequence. |
|
@u |
Speed up the matching operation by shuffling records in the channel. |
|
@m |
Can use MERGE method to perform the association when the channel is ordered by its foreign key and when the bin file/entity table is ordered by the primary key. |
Parameter:
|
ch |
A channel. |
|
C |
ch’s foreign key. |
|
f |
A bin file. |
|
T |
An entity table |
|
K |
The bin file/entity table’s key. |
|
x |
An expression of the field of f/T. |
|
F |
Name of the field of expression x. |
|
n |
Number of buffer rows. |
Return value:
Channel
Example:
Normal join between a channel and a bin file:
|
|
A |
|
|
1 |
=connect("demo").cursor("select CID,NAME,POPULATION,STATEID from CITIES") |
Return a cursor, whose data is as follows
|
|
2 |
=channel(A1) |
Create a channel and push cursor A1’s data to the channel. |
|
3 |
=file("States.btx") |
Return a segmented bin file, whose data is as follows:
|
|
4 |
=A2.joinx(STATEID,A3:STATEID,NAME:SName,POPULATION:SPopulation;1000) |
Match channel A2’s STATEID field with the bin file’s STATEID field, rename the latter’s NAME field and POPULATION field SName and SPopulation, add them to channel A2, and return a channel. |
|
5 |
=A1.fetch() |
A2 Fetch data from cursor A1, during which the data flows through the channel. |
|
6 |
=A2.result().fetch() |
Fetch data from the channel:
|
Use @i option during the association between a channel and an entity table to delete records that cannot match the foreign key:
|
|
A |
|
|
1 |
=connect("demo").cursor("select CID,NAME,POPULATION,STATEID from CITIES") |
Return a cursor, whose data is as follows:
|
|
2 |
=channel(A1) |
Create a channel and push cursor A1’s data to the channel. |
|
3 |
=file("StateFile.ctx").open() |
Return an entity table using STATEID as the key; below is data in the table:
|
|
4 |
=A2.joinx@i(STATEID,A3:STATEID,NAME:SName,POPULATION:SPopulation;1000) |
Match channel A2’s STATEID field with the entity table’s STATEID field, rename the latter’s NAME field and POPULATION field SName and SPopulation, add them to channel A2, and return a channel. Use @i option to delete records that cannot match channel A2’s foreign key STATEID. |
|
5 |
=A1.fetch() |
A2 Fetch data from cursor A1, during which the data flows through channel A2. |
|
6 |
=A2.result().fetch() |
Fetch data from the channel:
|
Use @d option for association between a channel and a bin file to perform filtering opposite to @i:
|
|
A |
|
|
1 |
=connect("demo").cursor("select CID,NAME,POPULATION,STATEID from CITIES") |
Return a cursor, whose data is as follows:
|
|
2 |
=channel(A1) |
Create a channel and push cursor A1’s data to the channel. |
|
3 |
=file("States.btx") |
Return a segmented bin file, whose data is as follows:
|
|
4 |
=A2.joinx@d(STATEID,A3:STATEID) |
Match channel A2’s STATEID field with the bin file’s STATEID field, and use @d option to filter away records from A2 that cannot match the bin file’s STATEID. |
|
5 |
=A1.fetch() |
Fetch data from cursor A1, during which the data flows through channel A2. |
|
6 |
=A2.result().fetch() |
Fetch data from the channel:
|
Use @m option to perform the MERGE algorithm:
|
|
A |
|
|
1 |
=connect("demo").cursor("select CID,NAME,POPULATION,STATEID from CITIES").sortx(STATEID) |
Return a cursor ordered by State field, whose data is as follows:
Data is ordered by STATEID. |
|
2 |
=channel(A1) |
Create a channel and push cursor A1’s data to the channel. |
|
3 |
=file("States.btx") |
Return a segmented bin file, whose data is as follows:
|
|
4 |
=A2.joinx@m(STATEID,A3:STATEID,NAME:SName,POPULATION:SPopulation;1000) |
As both A2 and A3 are ordered by STATEID, use @m option to perform MERGE algorithm. |
|
5 |
=A1.fetch() |
Fetch data from cursor A1, during which the data flows through channel A2 |
|
6 |
=A2.result().fetch() |
Fetch data from the channel:
|
