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

@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

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

 

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

24

=file("D:\\emp.ctx")

 

25

=A24.create()

 

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