joinx()

Read(245) Label: joinx,

Here’s how to use joinx() function.

joinx()

Description:

Join table sequences retrieved from a series of cursors.

Syntax:

joinx(csi:Fi,xj,..;…)

Note:

The function performs join@m 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 primay 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 relational 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.

 

Options:

@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

@u

Order by the key isn’t a must when cs1 is cursor while others are in-memory table sequences

Parameters:

csi

Cursors/table sequences being joined.

Fi

Field name of the result table sequence.

xj

Relational 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 ky isn’ required as only A28 is the cursor and others are in-memory table sequences

 

31

=A30.fetch()

Related functions:

join()

joinx()

Description:

Join up synchronously segmented cluser 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.

Options:

@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

Parameters:

Fi

The resulting field

csi

To-be-joined cluster cursors

xi

Joining field/expression

Return value:

Synchronously distributed cluster cursor

Example:

 

A

B

 

1

[192.168.0.114:8281,192.168.18.131:8281]

 

A cluster of two nodes

2

=file@0z("salespart.ctx", A1)

=file@0z("orderpart.ctx", A1)

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

3

=A2.create()

=B2.create()

Open the two composite tables respectively

4

=A3.cursor()

=B3.cursor()

Generate cluster cursors for the two cluster compositetables respectively

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@n("D:/t1.ctx","192.168.0.111:8281")

 

2

=A1.create()

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@n("D:/t2.ctx","192.168.0.111:8281")

 

5

=A4.create()

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

Options:

@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 cursor contains a relatively small amount of data or it is a sequence

@c

Speed up the matching action if the cursor is ordered by the first foreign key field; can work with @q option

@u

Speed up the matching operation by shuffling records in the cursor

Parameters:

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@o(F1;EID,A2:EMPLOYEEID,BONUS+1:SALARY1;5)

Use the original records as values of the new field F1

10

=A9.fetch()

11

=A1.joinx@o(F1;EID,A2:EMPLOYEEID,~:SALARY1;5)

Use ~ to represent parameter x

12

=A11.fetch()

13

=A1.joinx@d(EID,A2:EMPLOYEEID;5)

Keep records whose foreign key values are  mismatched when parameters x:F are absent

14

=A13.fetch()

15

=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

16

=A15.fetch()

16

=A1.joinx(EID,A2:#1,BONUS+1:SALARY1)

#1 represents the field whose number is 1, which is EMPLOYEEID here

17

=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. Parameter n is the number of buffer rows.

Options:

@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 cursor contains a relatively small amount of data or it is a sequence

@qc

Speed up the matching action if the cursor is ordered by the first foreign key field

@u

Speed up the matching operation by shuffling records in the cursor

Parameters:

cs

A cursor/multicursor

C

cs’s foreign key

T

An entity table/composite table cursor

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.create()

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@o(F1;EID,A3:EMPLOYEEID,BONUS+1:SALARY1;5)

Use the original records as values of the new field F1

11

=A10.fetch()

12

=A1.joinx@o(F1;EID,A3:EMPLOYEEID,~:SALARY1;5)

Use ~ to represent parameter x

13

=A12.fetch()

14

=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

15

=A14.fetch()

16

=demo.cursor("select * from PERFORMANCE")

Return a cursor

17

=file("D:/employees.ctx").create().cursor()

Return a composite table cursor

18

=A16.joinx(EMPLOYEEID,A17:EID,Name).fetch()

Join a cursor and a composite table cursor via the foreign key

19

=A1.joinx(EID,A3:#1,BONUS+1:SALARY1;5)

#1 represents the field whose number is 1, which is EMPLOYEEID here

20

=A19.fetch()

 

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. Parameter n is the number of buffer rows.

Options:

@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

@qc

Speed up the matching action if the channel is ordered by the first foreign key field

@u

Speed up the matching operation by shuffling records in the channel

Parameters:

ch

A channel

C

ch’s foreign key

f

A bin fiel

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

 

 

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.create()

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