joinx()
Here’s how to use joinx() functions.
joinx()
Description:
Join table sequences retrieved from a series of cursors.
Syntax:
joinx(csi:Fi,xj,..;…)
Note:
The function applies MERGE algorithm over a resulting set generated from a series of ordered cursors csi and returns a new cursor. If parameter xj is omitted, join the cursors according to their primary keys; if parameter xj is present but there isn’t any primary key, perform the join based on values of xj. The function can be also used to join multiple multicursors where each one must have the same number of parallel cursors or subcursor. Parameter csi can be a table sequence.
Supposing multiple cursors csi are already ordered by join field/expression xj, the function performs a join between them on the condition that xj’s value is equivalent to the value of x1 field and returns a cursor consisting of Fi,…. fields. Fi,…. are referencing fields that reference the records of the cursors csi. Note: xj… only supports the ascending order.
Regardless of the number of cursors being joined, the equivalence determination is conducted according to cursor cs1’s x1 field. Therefore, it is a one-to-many relationship.
Option:
|
@f |
Full join. If no matching records are found, then use nulls to correspond. |
|
@1 |
Left join. Note that it is the number “1”, instead of letter “l”. |
|
@p |
Perform a join according to positions, while ignoring the parameter xj |
|
@i |
Used only to filter A1 cs1 and ignore parameter Fi; do not work with @f@1 options |
|
@d |
Used only to filter cs1 to retain records that cannot be found and ignore parameter Fi; do not work with @f@1 options |
Parameter:
|
csi |
Cursors/table sequences being joined. |
|
Fi |
Field name of the result table sequence. |
|
xj |
Join field/expression. |
Return value:
A cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from EMPLOYEE order by EID" ) |
|
|
2 |
=demo.cursor("select * from PERFORMANCE order by EMPLOYEEID") |
|
|
3 |
=join@x(A1:EmployeeID1,EID;A2:EmployeeID2,EMPLOYEEID) |
Normal join. Discard the non-matching items, and each field value points to a record in the original cursor. |
|
4 |
=A3.fetch() |
|
|
5 |
=demo.cursor("select * from EMPLOYEE order by EID" ) |
|
|
6 |
=demo.cursor("select * from PERFORMANCE order by EMPLOYEEID") |
|
|
7 |
=joinx@f(A5:EmployeeID1,EID;A6: EmployeeID2,EMPLOYEEID) |
Full join. If no matching records, then use nulls to correspond. |
|
8 |
=A7.fetch() |
|
|
9 |
=demo.cursor("select * from EMPLOYEE order by EID" ) |
|
|
10 |
=demo.cursor("select * from PERFORMANCE order by EMPLOYEEID") |
|
|
11 |
=joinx@1(A9:EmployeeID2,EID-5;A10:EmployeeID1,EMPLOYEEID) |
Left join. The first cursor is regarded as the basis. If there are no matching records, then use null to correspond. |
|
12 |
=A11.fetch() |
|
|
13 |
=demo.cursor("select * from EMPLOYEE order by EID" ) |
|
|
14 |
=demo.cursor("select * from PERFORMANCE where EMPLOYEEID>3 order by EMPLOYEEID ") |
|
|
15 |
=joinx@p(A13:EmployeeID2;A14:EmployeeID1).fetch() |
Perform the join according to positions
|
|
16 |
=demo.query("select top 3 EID,NAME from EMPLOYEE") |
|
|
17 |
=A16.modify(2,2,null) |
|
|
18 |
=A16.cursor() |
|
|
19 |
=demo.cursor("select top 20 * from EMPLOYEE") |
|
|
20 |
=joinx(A18:Employee1,EID,NAME;A19: Employee2,EID,NAME) |
Perform a join by EID and NAME fields |
|
21 |
=A20.fetch() |
|
|
22 |
=demo.query("select top 3 EID,NAME from EMPLOYEE").keys(EID) |
|
|
23 |
=A22.cursor() |
|
|
24 |
=demo.query("select top 20 * from EMPLOYEE").keys(EID) |
|
|
25 |
=A24.cursor() |
|
|
26 |
=joinx(A23:Employee1;A25: Employee2) |
Perform the join according to primary keys |
|
27 |
=A26.fetch() |
|
|
28 |
=demo.cursor("select * from EMPLOYEE" ) |
|
|
29 |
=demo.query("select * from PERFORMANCE") |
|
|
30 |
=joinx@u(A28:EmployeeID1,EID;A29:EmployeeID2,EMPLOYEEID) |
With @u option, order by the key isn’ required as only A28 is the cursor and others are in-memory table sequences
|
|
31 |
=A30.fetch() |
|
|
32 |
=demo.cursor("select * from EMPLOYEE order by EID" ) |
|
|
33 |
=demo.cursor("select * from PERFORMANCE order by EMPLOYEEID") |
|
|
34 |
=joinx@i(A32,EID;A33,EMPLOYEEID) |
|
|
35 |
=A34.fetch() |
Filter A32 by retaining matching records |
|
36 |
=demo.cursor("select * from EMPLOYEE order by EID" ) |
|
|
37 |
=demo.cursor("select * from PERFORMANCE order by EMPLOYEEID") |
|
|
38 |
=joinx@d(A36,EID;A37,EMPLOYEEID) |
|
|
39 |
=A38.fetch() |
Filter A36 by retaining non-matching records |
Related function:
joinx()
Description:
Join up synchronously segmented cluster cursors.
Syntax:
joinx(csi:Fi,xi,..;…)
Note:
Suppose that the joining fields/expressions xi are ordered in an ascending order, the function joins up multiple synchronously segmented cluster cursors according to the condition that the values of the joining fields/expressions xi and x1 are equal to generate a cluster cursor consisting of fields Fi,…. Parameter Fi is a referencing field that references records of the csi, the sequence of the original cluster cursors. Join up the cluster cursors by primary keys when parameter xj is absent; and by the values of xj when the parameter is present but no dimensions are set. The function supports cluster multicursors that should contain the same number of subcursors. The join operation is performed by matching the joining field x1 in cs1 with the values of all the other joining fields/expressions, which means it is a one-to-many relationship. The to-be-joined cursors should be stored in one node; cross-node retrieval isn’t allowed.
Option:
|
@f |
Full join; use null to correspond when there is no matching value |
|
@1 |
Left join; the option is number 1 instead of letter l |
|
@p |
Join by positions; ignore parameter xj |
|
@i |
Used only to filter A1 cs1 and ignore parameter Fi; do not work with @f@1 options |
|
@d |
Used only to filter cs1 to retain records that cannot be found and ignore parameter Fi; do not work with @f@1 options |
Parameter:
|
Fi |
The resulting field |
|
csi |
To-be-joined cluster cursors |
|
xi |
Joining field/expression |
Return value:
Synchronously distributed cluster cursor
Example:
|
|
A |
|
|
1 |
=file("t1.ctx","192.168.0.111:8281") |
|
|
2 |
=A1.open() |
Open cluster composite table t1.ctx |
|
3 |
=A2.cursor@m(;;3) |
Generate a synchrounous 3-parts multicursor from A2’s cluster composite table |
|
4 |
=file("t2.ctx","192.168.0.111:8281") |
|
|
5 |
=A4.open() |
Open cluster composite table t2.ctx |
|
6 |
=A5.cursor(;;A3) |
Synchronously segment A5 according to A3’s multicursor |
|
7 |
=joinx(A3:t1,EID;A6:t2,EID).fetch() |
Join up A3 and A6 to perform a multithreading computation |
joinx()
Description:
Join up multiple pseudo tables.
Syntax：
joinx(Ti:Fi,xj,..;…)
Note:
The function joins multiple pseudo tables Ti,… together on the condition that the join field xj’s values are equivalent – by their primary keys when parameter xj is absent, and returns a pseudo table consisting of Fi,…. fields. Fi,…. are referencing fields that reference the records of the cursors csi. Note: xj… only supports the ascending order.
Regardless of the number of cursors being joined, the equivalence determination is conducted according to cursor T1’s x1 field. Therefore, it is a one-to-many relationship.
Parameter:
|
Ti |
Pseudo tables being joined |
|
Fi |
Field name of the result pseudo table |
|
xj |
The join field or join expression |
Option:
|
@f |
Full join; if no matching records are found, then use nulls to correspond |
|
@1 |
Left join; note that it is the number “1”, instead of letter “l” |
|
@p |
Perform the join according to positions while ignoring the parameter xj |
Return value:
A pseudo table
Example:
|
|
A |
|
|
1 |
=pseudo(create(file).record(["D:/file/pseudo/emp6.ctx"])) |
Generate a pseudo table object containing the following data:
|
|
2 |
=pseudo(create(file).record(["D:/file/pseudo/PERFORMANCE.ctx"])) |
Generate a pseudo table object containing the following data:
|
|
3 |
=joinx(A1:EID,EID;A2:EMPLOYEEID,EMPLOYEEID) |
A normal join that discards non-matching items and where each field value points to a record in the original cursor |
|
4 |
=A3.import() |
|
|
5 |
=joinx(A1:EID;A2:EMPLOYEEID) |
Join by primary keys as the join field is not specified and return the same result as above |
|
6 |
=A5.import() |
|
|
7 |
=joinx@f(A1:EID;A2:EMPLOYEEID) |
A full join that uses nulls to correspond when no matching records can be found |
|
8 |
=A7.import() |
|
|
9 |
=joinx@1(A1:EID;A2:EMPLOYEEID) |
A left join that uses the first pseudo table as the basis and that uses nulls to correspond when no matching records can be found |
|
10 |
=A9.import() |
|
|
11 |
=joinx@p(A1:EID;A2:EMPLOYEEID) |
Perform the join according to positions |
|
12 |
=A11.cursor().fetch() |
|
cs.joinx ()
Description:
Join up a cursor and a segmentable bin file according to the foreign key.
Syntax:
cs.joinx(C:…,f:K:…,x:F,…;…;…;n)
Note:
The function matches the foreign key fields C,… in cursor cs with the key K in segmentable bin file f, which is ordered by K, to find the corresponding record in f, 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. The cursor the function returns is irreversible.
Option:
|
@i |
Discard the records whose foreign key values can’t be matched |
|
@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; return a sequence when cursor/multicursor cs is a sequence |
|
@u |
Speed up the matching operation by shuffling records in the cursor |
|
@m |
Perform a merge join when cs is ordered by C and f is ordered by K; with this option, the bin file f can be a cursor, and when cs is a multicursor, it is the multicursor partitioned in the same way |
Parameter:
|
cs |
A cursor/multicursor |
|
C |
cs’s foreign key |
|
f |
A bin file |
|
K |
The bin file’s key; it is treated as row number when written as # |
|
x |
An expression of the field of f |
|
F |
Name of the field of expression x |
|
n |
Number of buffer rows |
Return value:
A cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Return a data retrieval cursor:
|
|
2 |
=file("D:\\joinx.btx") |
Return a bin file:
|
|
3 |
=A1.joinx(EID,A2:EMPLOYEEID,BONUS+1:SALARY1) |
Normal join; use null to represent the records whose foreign key values are mismatched |
|
4 |
=A3.fetch() |
|
|
5 |
=A1.joinx@i(EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5) |
Discard the records whose foreign key values are mismatched |
|
6 |
=A5.fetch() |
|
|
7 |
=A1.joinx@i(EID,A2:EMPLOYEEID;5) |
The result when parmeters x:F are absent when @i option works: |
|
8 |
=A7.fetch() |
|
|
9 |
=A1.joinx@d(EID,A2:EMPLOYEEID;5) |
Keep records whose foreign key values are mismatched when parameters x:F are absent |
|
0 |
=A19.fetch() |
|
|
11 |
=A1.joinx@q(EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5) |
Retrieve corresponding records from the bin file in order to match with the foreign key |
|
12 |
=A11.fetch() |
|
|
=A1.joinx(EID,A2:#1,BONUS+1:SALARY1) |
#1 represents the field whose number is 1, which is EMPLOYEEID here |
|
|
14 |
=A16.fetch() |
|
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 |
|
@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 |
|
@c |
Speed up the matching action if the cursor is ordered by the first foreign key field; when the cursor is ordered by the first field of its foreign key, use it to speed up the join; can work with @q |
|
@u |
Speed up the matching operation by shuffling records in the cursor |
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:
A cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Return a data retrieval cursor:
|
|
2 |
=file("D:\\PERFORMANCE3.ctx") |
|
|
3 |
=A2.open() |
Open an entity table:
|
|
4 |
=A1.joinx(EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Normal join; use null to represent the records whose foreign key values are mismatched |
|
5 |
=A4.fetch() |
|
|
6 |
=A1.joinx@i(EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Discard the records whose foreign key values are mismatched |
|
7 |
=A6.fetch() |
|
|
8 |
=A1.joinx@d(EID,A3:EMPLOYEEID;5) |
Keep records whose foreign key values are mismatched when parameters x:F are absent |
|
9 |
=A8.fetch() |
|
|
10 |
=A1.joinx@q(EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Retrieve corresponding records efficiently from the entity table in order to match with the foreign key |
|
11 |
=A10.fetch() |
|
|
12 |
=A1.joinx(EID,A3:#,BONUS+1:SALARY1;5) |
Take A1’s EID as the record number to join with A3’s records |
|
13 |
=A12.fetch() |
|
|
14 |
=file("D:\\emp1.ctx") |
|
|
15 |
=A14.open() |
|
|
16 |
=A1.joinx@qc(EID:DEPT,A15:EID:DEPT,STATE:EMPSTATE;5) |
Use @qc options to get matching records efficiently and quickly |
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 to generate a new channel. 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 |
|
@o(F;…) |
Generate a new record by adding a F field whose values are referenced records; expression x can be represented by ~, which means a record |
|
@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 channel contains a relatively small amount of data or it is a sequence |
|
@c |
Speed up the matching action if the channel is ordered by the first foreign key field; can work with @q |
|
@u |
Speed up the matching operation by shuffling records in the channel |
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:
Bin file:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Return a data retrieval cursor:
|
|
2 |
=file("D:\\joinx.btx") |
Return a bin file:
|
|
3 |
=channel() |
Create a channel |
|
4 |
=channel() |
|
|
5 |
=channel() |
|
|
6 |
=channel() |
|
|
7 |
=channel() |
|
|
8 |
=channel() |
|
|
9 |
=A1.push(A3,A4,A5,A6,A7,A8) |
Join cursor A1 to channels A3, A4, A5, A6, A7 and A8, and be ready to push data into the channels |
|
10 |
=A3.joinx(EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5) |
Normal join; use null to represent the records whose foreign key values are mismatched |
|
11 |
=A4.joinx@i(EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5) |
Discard the records whose foreign key values are mismatched |
|
12 |
=A5.joinx@o(F1;EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5) |
Use the original records as values of the new field F1 |
|
13 |
=A6.joinx@o(F1;EID,A2:EMPLOYEEID,~:SALARY1;5) |
Use ~ to represent parameter x |
|
14 |
=A7.joinx@d(EID,A2:EMPLOYEEID;5) |
Keep records whose foreign key values are mismatched when parameters x:F are absent |
|
15 |
=A8.joinx@q(EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5) |
Retrieve corresponding records from the bin file in order to match with the foreign key |
|
16 |
=A1.fetch() |
Push data in cursor A1 into the channels |
|
17 |
=A3.result().fetch() |
|
|
18 |
=A4.result().fetch() |
|
|
19 |
=A5.result().fetch() |
|
|
20 |
=A6.result().fetch() |
|
|
21 |
=A7.result().fetch() |
|
|
22 |
=A8.result().fetch() |
|
|
24 |
=file("D:\\emp.btx") |
|
|
25 |
=channel() |
|
|
26 |
=A1.push(A25) |
|
|
27 |
=A25.joinx@qc(DEPT:EID,A24:EID:DEPT,SALARY+1:SALARY1;5) |
Use @qc options to get matching records efficiently and quickly |
Entity table:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Return a data retrieval cursor:
|
|
2 |
=file("D:\\PERFORMANCE3.ctx") |
|
|
3 |
=A2.open() |
Open an entity table:
|
|
4 |
=channel() |
Create a channel |
|
5 |
=channel() |
|
|
6 |
=channel() |
|
|
7 |
=channel() |
|
|
8 |
=channel() |
|
|
9 |
=channel() |
|
|
10 |
=A1.push(A4,A5,A6,A7,A8,A9) |
Join cursor A1 to channels A3, A4, A5, A6, A7 and A8, and be ready to push data into the channels |
|
11 |
=A4.joinx(EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Normal join; use null to represent the records whose foreign key values are mismatched |
|
12 |
=A5.joinx@i(EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Discard the records whose foreign key values are mismatched |
|
13 |
=A6.joinx@o(F1;EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Use the original records as values of the new field F1 |
|
14 |
=A7.joinx@o(F1;EID,A3:EMPLOYEEID,~:SALARY1;5) |
Use ~ to represent parameter x |
|
15 |
=A8.joinx@d(EID,A3:EMPLOYEEID;5) |
Keep records whose foreign key values are mismatched when parameters x:F are absent |
|
16 |
=A9.joinx@q(EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5) |
Retrieve corresponding records from the entity table in order to match with the foreign key |
|
17 |
=A1.fetch() |
Push data in cursor A1 into the channels |
|
18 |
=A4.result().fetch() |
|
|
19 |
=A5.result().fetch() |
|
|
20 |
=A6.result().fetch() |
|
|
21 |
=A7.result().fetch() |
|
|
22 |
=A8.result().fetch() |
|
|
23 |
=A9.result().fetch() |
|
|
24 |
=file("D:\\emp.ctx") |
|
|
25 |
=A24.open() |
|
|
26 |
=channel() |
|
|
27 |
=A1.push(A26) |
|
|
28 |
=A26.joinx@qc(DEPT:EID,A25:EID:DEPT,SALARY+1:SALARY1;5) |
Use @qc options to get matching records efficiently and quickly |