T.pjoin ()
Description:
Define an association computation between a pseudo table and a cursor/record sequence through the join key on a pseudo table.
Syntax:
T.pjoin(K:..,x:F,…; csi:z,Ki:…,xi:Fi,…; …)
Note:
The function defines a computation on pseudo table T, which associates T and cursor/record sequence csi through the join key, by which they need to be ordered, and returns a sequence consisting of x:F,… and xi:Fi,….
csi can be a cursor or a records sequence. by default, x:F,… involves all fields of T; both T and csi should be ordered by the join key.
When relationship between T and csi is one-to-many, xi is an aggregate expression.
When relationship between T and csi is many-to-one, records of csi will appear repeatedly in the result set.
Parameter z specifies the join type. It can be absent or null. Perform an inner join when z is absent, and a left join when z is null; and only retain records of T that cannot find matches when z is null and both parameters xi:Fi are absent.
Parameter:
|
T |
A pseudo table |
|
K |
T’s join key |
|
x |
T’s expression |
|
F |
Field name corresponding to expression x |
|
csi |
A cursor/record sequence |
|
z |
Join type |
|
Ki |
Join key of csi |
|
xi |
Expression of csi |
|
Fi |
Field name corresponding to expression xi |
Return value:
Pseudo table
Option:
|
@f |
Enable full join while ignoring parameter z; do not work with @r |
Example:
Multi-homo-dimension table join:
|
|
A |
|
|
1 |
=create(file).record(["statename-pj.ctx"]) |
Below is content of statename-pj.ctx, which is ordered by STATEID:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").query("select STATEID,POPULATION from STATEINFO").sort(STATEID) |
Return a record sequence ordered by STATEID.
|
|
4 |
=connect("demo").query("select STATEID,CAPITAL from STATECAPITAL").sort(STATEID) |
Return a record sequence ordered by STATEID.
|
|
5 |
=A2.pjoin(STATEID,STATEID:ID,NAME;A3,STATEID,POPULATION;A4,STATEID,CAPITAL) |
Define a computation on A2’s pseudo table, which will create association between the pseudo table and record sequences A3 and A4 through join key STATEID, and rename STATEID ID, and return a new pseudo table.
|
|
6 |
=A5.cursor().fetch() |
Fetch data from A5’s pseudo table while executing the computation defined in A5 on A2’s pseudo table, and return the following table:
|
When T and csi has a one-to-many relationship:
|
|
A |
|
|
1 |
=create(file).record(["dep-pj.ctx"]) |
Below is content of composite table dep-pj.ctx, which is ordered by DEPT:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=demo.cursor("select EID,NAME,DEPT from EMPLOYEE").sortx(DEPT) |
Below is content of returned cursor:
|
|
4 |
=A2.pjoin(DEPT;A3,DEPT,count(EID):Num) |
Pseudo table A2 and cursor A3 have a one-to-many relationship; define a computation on A2’s pseudo table, which will associate them through DEPT, find the number of EID values under each DEPT value and make the computing results a new field Num, and return a new pseudo table.
|
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
When T and csi has a many-to-one relationship:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Below is content of composite table cities-pj.ctx, which is ordered by STATEID:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor and below is the content:
|
|
4 |
=A2.pjoin(STATEID,CID,NAME,POPULATION;A3,STATEID,CAPITAL ) |
Pseudo table A2 and cursor A3 have a many-to-one relationship; define a computation on A2’s pseudo table, which will associate them through STATEID, during which A2’s field values appear in the result set repeatedly, and return a new pseudo table.
|
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
Full join:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Composite table cities-pj.ctx is ordered by STATEID; its content is as follows:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; content is as follows:
|
|
4 |
=A2.pjoin@f(STATEID,CID,NAME,POPULATION;A3,STATEID,CAPITAL ) |
Define a computation on A2’s pseudo table – with @f option, perform a full join to display record field values that do not have matches as nulls, and return a new pseudo table.
|
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
Method 1 for left join:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Composite table cities-pj.ctx is ordered by STATEID; its content is as follows:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table. |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor whose data is as follows:
|
|
4 |
=A2.pjoin(STATEID,CID,NAME,POPULATION;A3:null,STATEID,CAPITAL ) |
Define a computation on A2’s pseudo table – as parameter z is null, perform a left join to list all records of the pseudo table and display field values that do not match A3 as nulls – and return a new pseudo table. |
|
5 |
=A4.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
Method 2 for left join:
|
|
A |
|
|
1 |
=create(file).record(["cities-pj.ctx"]) |
Composite table cities-pj.ctx is ordered by STATEID; its content is as follows:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=connect("demo").cursor("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sortx(STATEID) |
Return a cursor; its content is as follows:
|
|
4 |
=A2.pjoin(STATEID;A3:null,STATEID) |
Define a computation on A2’s pseudo table – as parameter z is null and parameters xi:Fi are absent, only retain pseudo table records that do not have matches, and return a new pseudo table.
|
|
5 |
=A3.import() |
Fetch data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and return the following table:
|
