joinx()

Read(114) Label: join, cursor,

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

 

31

=A30.fetch()

Related functions:

join()