joinx()

Read(1900) Label: 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: xjonly 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.

This is a delayed function.

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:

Cursor

Example:

 

A

 

1

=demo.cursor("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE " )

Return a cursor, whose data is as follows:

2

=demo.cursor("select  top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " )

Return a cursor, whose data is as follows:

3

=joinx(A1:Emp,EID;A2:Family,EID)

A normal join that discards every non-matching record, during which each field value in the result set points to a record in the original cursor.

4

=A3.fetch()

Fetch data from cursor A3:

 

Join by the primary key:

 

A

 

1

=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE " ).keys(EID).cursor()

Return a cursor where the table’s primary key is EID and whose data is as follows:

2

=demo.query("select  top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " ).keys(EID)

Return a table sequence whose key is EID and whose data is as follows:

3

=joinx(A1:Emp;A2:Family)

Join by the primary key.

4

=A3.fetch()

Fetch data from cursor A3:

 

Use @f option to perform full join:

 

A

 

1

=demo.cursor("select  EID,NAME,DEPT,GENDER from EMPLOYEE where EID>2 and EID<10" )

Return a cursor whose data is as follows:

2

=demo.query("select  top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " )

Return a cursor whose data is as follows:

3

=joinx@f(A1:Emp,EID;A2:Family,EID)

Use @f option to enable a full join that records a field as null when no matching value can be found.

4

=A3.fetch()

Fetch data from cursor A3:

 

Use @1 to perform left join:

 

A

 

1

=demo.cursor("select  EID,NAME,DEPT,GENDER from EMPLOYEE where EID>2 and EID<10" )

Return a cursor whose data is as follows:

2

=demo.query("select  top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " )

Return a cursor whose data is as follows:

3

=joinx@1(A1:Emp,EID;A2:Family,EID)

Use @1 option to enable left join based on the first cursor and that records a value that does not have a matching value in another cursor as null.

4

=A3.fetch()

Fetch data from cursor A3:

 

Use @p option to perform position-based join:

 

A

 

1

=demo.cursor("select  EID,NAME,DEPT,GENDER from EMPLOYEE where EID>2 and EID<10" )

Return a cursor whose data is as follows:

2

=demo.query("select  top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " )

Return a cursor whose data is as follows:

3

=joinx@p(A1:Emp,EID;A2:Family,EID)

Use @p option to perform position-based join.

4

=A3.fetch()

Fetch data from cursor A3:

 

Use @i option to enable a filtering operation only:

 

A

 

1

=demo.cursor("select  top 10 EID,NAME,DEPT,GENDER from EMPLOYEE" ).sortx(DEPT)

Return a cursor whose data is as follows:

2

=demo.query("select  top 5 *  from DEPARTMENT " ).sort(DEPT)

Return a table sequence whose data is as follows:

3

=joinx@i(A1,DEPT;A2,DEPT)

Use @i option to filter cursor A1, during which records that match A2 are retained.

4

=A3.fetch()

Fetch data from cursor A3:

 

Use @d option to enable a filtering operation opposite to @i:

 

A

 

1

 

Return a cursor whose data is as follows:

2

=demo.query("select  top 5 *  from DEPARTMENT " ).sort(DEPT)

Return a table sequence whose data is as follows:

3

=joinx@d(A1,DEPT;A2,DEPT)

Use @d option to filter cursor A1, during which records that do not match A2 are retained.

4

=A3.fetch()

Fetch data from cursor A3:

 

Related function:

join()

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:

Cluster cursor

Example:

 

A

B

 

1

[192.168.0.114:8281,192.168.18.131:8281]

 

A cluster of two nodes.

2

=file("salespart.ctx", A1)

=file("orderpart.ctx":[1], A1)

Composite tables salespart.ctx  and orderpart.ctx are stored on the two nodes by segments.

 

3

=A2.open()

=B2.open()

Open the two composite tables respectively.

4

=A3.cursor()

=B3.cursor()

 

5

=B4.sortx(EID; A4)

=joinx(A4:s,EID;A5:o,EID)

A5 sorts B4’s result, distributes it according to the distribution way of salespart.ctx and generate a cursor for it; B5 joins up the two synchronously distributed cluster cursors.

 

 

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.

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 parameters 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 mismatch 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()

13

=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.