ch.fjoin ()
Description:
Attach a foreign-key-style association on a channel and return the original channel.
Syntax:
ch.fjoin(w:T,x:F,…;…)
Note:
The function attaches a computation to channel ch, which will compute expression w and then expression x over each of its rows, and adding new field F to ch which uses results of x as its values, and returns the original channel.
T is w’s alias, which can be referenced in expression x; when x is represented by ~, it is treated as w; re-assign field F when it already exists in channel ch.
A primary table and a subtable can be associated through the foreign key in parameter w.
This is an attachment function.
Parameter:
|
ch |
A channel |
|
w |
An expression, which, besides the regular syntax, can be used in the following syntax: 1. K=w, which means value assignment; K is a field of channel ch, and you can use an esProc function in w; 2. (Ki=wi,…,w), which contains the combined use of Ki=wi, where w is a logical expression, and where Ki can be referenced in w |
|
T |
Alias of expression w; can be absent |
|
x |
An expression; can be absent |
|
F |
Field name in expression x; can be absent |
Return value:
Channel
Option:
|
@i |
Delete the current record when result of expression w is null or false |
Example:
When using syntax K=w:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor; below is the content:
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=A1.push(A2) |
Be ready to push cursor A1’s data into the channel; the action needs to wait. |
|
4 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence using STATEID as the primary key.
|
|
5 |
=A2.fjoin(STATEID=A4.find(STATEID)) |
Attach a computation to channel A2, which will use syntax k=w to associate channel A2 with table sequence A4 through the former ’s foreign key STATEID and assign corresponding referencing records in A4 to A2’s foreign key and nulls to the non-matching records, and return the original channel. |
|
6 |
=A2.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
7 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data into the channel to execute the attached computation and keep the result |
|
8 |
=A2.result() |
Retrieve channel A2’s result.
|
When using @i option:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor; below is the content:
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=A1.push(A2) |
Be ready to push cursor A1’s data into the channel; the action needs to wait.. |
|
4 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence using STATEID as the primary key.
|
|
5 |
=A2.fjoin@i(STATEID=A4.find(STATEID)) |
Attach a computation in channel A2, which will use @i option to create association between cursor A1 and table A2 through the former’s foreign key STATEID, during which whole records of A1 are deleted when their foreign key values cannot find matches, and view the result in A8. |
|
6 |
=A2.fetch() |
Execute the result set function in channel A2 and return the original channel. |
|
7 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data into the channel to execute the attached computation and keep the result |
|
8 |
=A2.result() |
Retrieve channle A2’s result.
|
When using syntax (Ki=wi,…,w):
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return a cursor; below is the content:
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=channel() |
Create a channel. |
|
4 |
=A1.push(A2,A3) |
Be ready to push cursor A1’s data to channel A2 and A3; the action needs to wait. |
|
5 |
=6.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence using ID as the key.
|
|
6 |
=create(name,gender).record(["Rebecca","F","Ashley","F","Matthew","M"]).keys(name) |
Generate a table sequence using name as the key.
|
|
7 |
=A2.fjoin@i((EID=A5.find(EID),NAME=A6.find(NAME),EID!=null&&NAME!=null)) |
Attach a computation in channel A2, where channel A2 associates with table sequence A5 through foreign key field EID and with table sequence A6 through foreign key field NAME, during which corresponding referencing records in A5 are assigned to EID, matching referencing records in A6 are assigned to NAME and A2’s records that cannot find matches are deleted, and return the original channel A2. |
|
8 |
=A2.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
9 |
=A3.fjoin@i((EID=A5.pfind(EID),NAME=A6.pfind(NAME),EID!=null&&NAME!=null)) |
Attach a computation in channel A3, where channel A3 associates with table sequence A5 through foreign key field EID and with table sequence A6 through foreign key field NAME, during which corresponding referencing records in A5 are assigned to EID, matching referencing records in A6 are assigned to NAME and A3’s records that cannot find matches are deleted, and view the computing result in A13. |
|
10 |
=A3.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
11 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data into the channel to execute the attached computation and keep the result |
|
12 |
=A2.result() |
Retrieve channel A2’s result.
|
|
13 |
=A3.result() |
Retrieve channel A3’s result.
|
When using other syntax:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return cursor for employee table; below is the data:
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=channel() |
Create a channel. |
|
4 |
=A1.push(A2,A3) |
Be ready to push cursor A1’s data to channel A2 and A3; the action needs to wait. |
|
5 |
=A2.fjoin(age(BIRTHDAY):age,age>50:ifRetire) |
Attach a computation to channel A2, which will get age of each employee in cursor A1 – use age as the alias of age computation, find if each age is above 50 and add results of computing expression age>50 to A1 as a new field named ifRetire, and return the original channel A2. |
|
6 |
=A2.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
7 |
=A3.fjoin(age(BIRTHDAY),~:AGE) |
Attach a computation to channel A3, where parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY), and return the original channel A3. |
|
8 |
=A3.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
9 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data into the channel to execute the attached computation and keep the result. |
|
10 |
=A2.result() |
Retrieve channle A2’s result.
|
|
11 |
=A3.result() |
Retrieve channle A3’s result.
|
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return a cursor whose data is as follows:
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=A1.push(A2) |
Be ready to push cursor A1’s data to channel A2; the action needs to wait. |
|
4 |
=A2.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
Attach a computation to channel A2, where parameter w is the value assignment format - return female when GENDER value is F, otherwise return male; since w’s alias GENDER already exists in the channel, the function won’t generate a new field but assigns new values to the channel’s GENDER field, and return the original channel A2. |
|
5 |
=A2.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data into the channel to execute the attached computation and keep the result. |
|
7 |
=A2.result() |
Retrieve channle A2’s result.
|
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor.
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=A1.push(A2) |
Be ready to push cursor A1’s data into the channel; the action needs to wait.. |
|
4 |
=A2.fjoin@i(CID<5) |
Attach a computation in channel A2, which will use @i option to get records where CID is less than 5 from the channel, during which whole records will be deleted if result of the expression is null or false, and return the original channel A2. |
|
5 |
=A2.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data into the channel to execute the attached computation and keep the result. |
|
7 |
=A2.result() |
Retrieve channle A2’s result.
|
