cs.joinx()
Description:
Join up a cursor and an entity table by the foreign key.
Syntax:
cs.joinx(C:…,T:K:…,x:F,…;…;…;n)
Note:
The function matches the foreign key fields C,… in cursor cs with the key K in entity table T, which is ordered by K, to find the corresponding record in T, and makes the record’s expression x a new field F and adds it to cs to generate a new cursor. The value corresponding to a mismatched record will be recorded as null.
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 cursor/multicursor |
|
@d |
When parameters x:F are absent, discard the records whose foreign key values are matching |
|
@q |
Speed up the matching action according to a certain order when the cursor contains a relatively small amount of data or it is a sequence cs Speed up the join when the cursor contains relatively small amount of data or is a sequence |
|
@u |
Speed up the matching operation by shuffling records in the cursor |
|
@m |
Can use MERGE method to perform the association when the cursor/multicursor is ordered by its foreign key and when the entity table is ordered by the primary key |
Parameter:
|
cs |
A cursor/multicursor |
|
C |
cs’s foreign key |
|
T |
An entity table |
|
K |
The entity table’s key; it is treated as row number when written as # |
|
x |
An expression of the field of T |
|
F |
Name of the field of expression x |
|
n |
Number of buffer rows |
Return value:
Cursor/Table sequence
Example:
Normal join between a cursor and an entity table:
|
|
A |
|
|
1 |
=connect("demo").cursor("select CID,NAME,POPULATION,STATEID from CITIES") |
Return a cursor, whose data is as follows:
|
|
2 |
=file("StateFile.ctx").open() |
Return an entity table using STATEID as the key; below is data in the table:
|
|
3 |
=A1.joinx(STATEID,A2:STATEID,NAME:SName,POPULATION:Spopulation;1000) |
Match cursor A1’s STATEID field with the entity table’s STATEID field, rename the latter’s NAME field and POPULATION field SName and SPopulation, and piece them to cursor A1 to generate a new cursor. |
|
4 |
=A3.fetch@x(100) |
Fetch the first 100 records from A3’s cursor, close the cursor when the fetching is finished, and return the following data:
|
Use @i option to delete records that do not 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 |
=file("StateFile.ctx").open() |
Return an entity table using STATEID as the key; below is data in the table:
|
|
3 |
=A1.joinx@i(STATEID,A2:STATEID,NAME:SName,POPULATION:SPopulation;1000) |
Use @i option to delete records that cannot match cursor A1’s foreign key STATEID. |
|
4 |
=A3.fetch@x(100) |
Fetch the first 100 records from A3’s cursor, close the cursor when the fetching is finished, and return the following data:
|
Use @d option:
|
|
A |
|
|
1 |
=connect("demo").cursor("select CID,NAME,POPULATION,STATEID from CITIES") |
Return a cursor, whose data is as follows:
|
|
2 |
=file("StateFile.ctx").open() |
Return an entity table using STATEID as the key; below is data in the table:
|
|
3 |
=A1.joinx@d(STATEID,A2:STATEID) |
Use @d option to retain records that cannot match cursor A1’s foreign key STATEID. |
|
4 |
=A3.fetch@x(100) |
Fetch the first 100 records from A3’s cursor, close the cursor when the fetching is finished, and return the following data:
|
Use @q option when parameter cs is a table sequence:
|
|
A |
|
|
1 |
=connect("demo").query("select CID,NAME,POPULATION,STATEID from CITIES") |
Return a table sequence, whose data is as follows:
|
|
2 |
=file("StateFile.ctx").open() |
Return an entity table using STATEID as the key; below is data in the table:
|
|
3 |
=A1.joinx@qi(STATEID,A2:STATEID,NAME:SName,POPULATION:SPopulation;1000) |
Return a table sequence when parameter cs is a table sequence; use @q option to speed up the computation and return the following result
|
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 STATEID field, whose data is as follows:
|
|
2 |
=file("StateFile.ctx").open() |
Return an entity table using STATEID as the key; below is data in the table:
|
|
3 |
=A1.joinx@m(STATEID,A2:STATEID,NAME:SName,POPULATION:SPopulation;1000) |
As both A1 and A2 are ordered by STATEID, use @m option to perform MERGE algorithm. |
|
4 |
=A3.fetch@x(100) |
Fetch the first 100 records from A3’s cursor, close the cursor when the fetching is finished, and return the following data:
|
