cs .join()

Description:

A foreign-key-style join between a cursor and a table sequence/record sequence. 

Syntax:

cs.join(C:.,T:K,x:F,…; …;…)

Note:

The function matches foreign key field C of cursor cs with the key of parameter T to find the corresponding records in T. Add an F field represented by x, which is T’s field expression, to cs and return the original cursor. K can be omitted or represented by #. When omitted, K is T’s key; when written as #, K is represented by a sequence number. It supports the multicursor. 

Parameters:

cs

A cursor/A multicursor

C

Cursor cs’s foreign key; separate multiple fields in a composite key with the colon

T

A table sequence/A record sequence

K

T’s key

x

T’s field expression

F

Field name in expression x.

Options:

@i

Delete a record with a non-matching foreign key value; by default a non-matching record will be represented by null

@o(F;…)

Use the record as the value of F field to generate a new record; here expression x can be represented by ~, which is T’s record

@d

If parameters x:F are absent, only perform a filtering over the cursor by deleting its records where the foreign key is matched

Return value:

The orignal cursor

Example:

 

 

A

 

1

=demo.cursor("select EID,NAME,DEPT,SALARY from

EMPLOYEE where EID<10 order by  EID" )

Return retrieved data as a cursor:

2

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").keys(EMPLOYEEID)

Return a table sequence:

3

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

Normal join; a non-matching record will be displayed as null

4

=A3.fetch()

5

=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by  EID" )

Same as A1

6

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").keys(EMPLOYEEID)

Same as A2

7

=A5.join@i(EID,A6, BONUS+1:SALARY1)

Delete non-matching records

8

=A7.fetch()

9

=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by  EID" )

Same as A1

10

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").keys(EMPLOYEEID)

Same as A2

11

=A9.join@i(EID,A10: #1, #3+1:SALARY1)

#1 represents the 1st field; #3 represents the 3rd field

12

=A11.fetch()

Same as A8

13

=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by  EID" )

Same as A1

14

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").keys(EMPLOYEEID)

Same as A2

15

=A13.join@o(F1;EID,A14,BONUS+1:SALARY1)

Use records as values of F1field

16

=A15.fetch()

17

=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by  EID" )

Same as A2

18

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").keys(EMPLOYEEID)

Same as A2

19

=A17.join@o(F1;EID,A18,~:SALARY1)

Replace parameter x with the sign ~

20

=A19.fetch()

21

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

 

22

=A21.create(#EMPLOYEEID,EVALUATION,BONUS;EMPLOYEEID

Create a composite table

23

=A22.attach(table3,#EMPLOYEEID,EVALUATION,BONUS)

Add an attached table

24

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

Return a table sequence

25

=A23.append(A24)

Append records to A23’s attached table

26

=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by  EID" )

 

Related functions:

join()