joinx()

Read(37) Label: pseudo table, join,

Description:

Join up multiple pseudo tables.

Syntax

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

Note:

The function joins multiple pseudo tables Ti,… together on the condition that the join field xj’s values are equivalent – by their primary keys when parameter xj is absent, and returns a pseudo table 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 T1’s x1 field. Therefore, it is a one-to-many relationship.

Parameters:

Ti

Pseudo tables being joined

Fi

Field name of the result pseudo table

xj

The join field or join expression

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 the join according to positions while ignoring the parameter xj

Return value:

A pseudo table

Example:

 

A

 

1

=pseudo(create(file).record(["D:/file/pseudo/emp6.ctx"]))

Generate a pseudo table object containing the following data:

2

=pseudo(create(file).record(["D:/file/pseudo/PERFORMANCE.ctx"]))

Generate a pseudo table object containing the following data:

3

=joinx(A1:EID,EID;A2:EMPLOYEEID,EMPLOYEEID)

A normal join that discards non-matching items and where each field value points to a record in the original cursor

4

=A3.import()

5

=joinx(A1:EID;A2:EMPLOYEEID)

Join by primary keys as the join field is not specified and return the same result as above

6

=A5.import()

 

7

=joinx@f(A1:EID;A2:EMPLOYEEID)

A full join that uses nulls to correspond when no matching records can be found

8

=A7.import()

9

=joinx@1(A1:EID;A2:EMPLOYEEID)

A left join that uses the first pseudo table as the basis and that uses nulls to correspond when no matching records can be found

10

=A9.import()

11

=joinx@p(A1:EID;A2:EMPLOYEEID)

Perform the join according to positions

12

=A11.cursor().fetch()