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

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

21

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

 

22

=A21.create()

 

23

=A1.joinx@qc(EID:DEPT,A22:EID:DEPT,STATE:EMPSTATE;5)

Use @qc options to get matching records efficiently and quickly