fjoin()
Here are how to use fjoin() functions.
P.fjoin ()
Perform foreign-key-style association on a table sequence/record sequence.
Syntax:
P.fjoin(w:T,x:F,…;…)
Note:
The function computes expression w over each row of table sequence/record sequence P and then expression x on w to generate a new field F, and returns a new table sequence consisting of P and F.
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 P.
A primary table and a subtable can be associated through the foreign key in parameter w.
Parameter:
|
P |
A table sequence/record sequence |
|
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 P, and you can use an esProc function in w; 2. (Ki=wi,…,w), which contains the combined use of Ki=wi 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:
Table sequence
Option:
|
@i |
Delete the current record when result of expression w is null or false |
|
@m |
Enable parallel processing |
Example:
When using syntax K=w:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
|
|
2 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose primary key is STATEID
|
|
3 |
=A1.fjoin(STATEID=A2.find(STATEID)) |
Use the syntax k=w to associate table A2 and table A1 through the latter’s foreign key STATEID, and return a result set by assigning A2’s corresponding referencing records to A1’s foreign key and nulls to the non-matching records
|
When @i option is present:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a table sequence
|
|
2 |
=A1.fjoin@i(CID<5) |
Use @i option to get records where CID is less than 5 from table sequence A1, during which whole records will be deleted if result of the expression is null or false |
|
3 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
|
|
4 |
=A1.fjoin@i(STATEID=A3.find(STATEID)) |
Use @i option to associate table A3 and A1 through the latter’s foreign key STATEID, during which whole records of A1 will be deleted if the foreign key value cannot find a match in A3
|
When using syntax (Ki=wi,…,w):
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT from employee") |
Return a table sequence
|
|
2 |
=6.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence using ID as the key
|
|
3 |
=create(name,gender).record(["Rebecca","F","Ashley","F","Matthew","M"]).keys(name) |
Generate a table sequence using name as the key
|
|
4 |
=A1.fjoin@i((EID=A2.find(EID),NAME=A3.find(NAME),EID!=null&&NAME!=null)) |
A1 associates with A2 through foreign key field EID and with A3 through foreign key field NAME by switching EID to A2’s referencing records and NAME to A3’s referencing records, while deleting non-matching records in A1
|
|
5 |
=A1.fjoin@i((EID=A2.pfind(EID),NAME=A3.pfind(NAME),EID!=null&&NAME!=null)) |
A1 associates with A2 through foreign key field EID and with A3 through foreign key field NAME, by assigning ordinal numbers of the corresponding key values in A2 to A1’s foreign key field EID and assigning ordinal numbers of the corresponding key values in A3 to its foreign key field NAME, while deleting non-matching records from A1
|
When using other syntax:
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return data of employee table:
|
|
2 |
=A1.fjoin(age(BIRTHDAY):age,age>50:ifRetire) |
Get age of each employee in table sequence A1 – use age as the alias of age computation, and then find if each age is above 50, and add results of computing expression age>50 to table sequence A1 as a new field named ifRetire:
|
|
3 |
=A1.fjoin(age(BIRTHDAY),~:AGE) |
The parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY):
|
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return a table sequence 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 table sequence, the function won’t generate a new field but assigns new values to table sequence A1’s GENDER field:
|
|
2 |
=A1.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
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 table sequence, the function won’t generate a new field but assigns new values to table sequence A1’s GENDER field:
|
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return a table sequence
|
|
2 |
=A1.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
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 table sequence, the function won’t generate a new field but assigns new values to table sequence A1’s GENDER field:
|
cs.fjoin ()
Description:
Perform foreign-key-style association on a cursor.
Syntax:
cs.fjoin(w:T,x:F,…;…)
Note:
The function computes expression w over each row of cursor A and then expression x on w to generate a new field F, and returns the original cursor consisting of cs and F.
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 cursor cs.
A primary table and a subtable can be associated through the foreign key in parameter w.
Parameter:
|
cs |
A cursor |
|
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 cursor cs, and you can use an esProc function in w; 2. (Ki=wi,…,w), which contains the combined use of Ki=wi 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:
Cursor
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:
|
|
2 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose primary key is STATEID
|
|
3 |
=A1.fjoin(STATEID=A2.find(STATEID)) |
Use syntax k=w to associate table A2 and table A1 through the latter’s foreign key STATEID, and return a result set by assigning corresponding referencing records in A2 to A1’s foreign key and nulls to the non-matching records; below is content of the returned cursor:
|
When @i option is present:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor
|
|
2 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
|
|
3 |
=A1.fjoin@i(STATEID=A2.find(STATEID)) |
Use @i option to associate table A2 and A1 through the latter’s foreign key STATEID, during which whole records of A1 will be deleted if the foreign key value cannot find a match in A2; below is content the returned cursor:
|
When using syntax (Ki=wi,…,w):
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT from employee") |
Return a cursor
|
|
2 |
=6.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence using ID as the key
|
|
3 |
=create(name,gender).record(["Rebecca","F","Ashley","F","Matthew","M"]).keys(name) |
Generate a table sequence using name as the key
|
|
4 |
=A1.fjoin@i((EID=A2.find(EID),NAME=A3.find(NAME),EID!=null&&NAME!=null)) |
A1 associates with A2 through foreign key field EID and with A3 through foreign key field NAME by switching EID to A2’s referencing records and NAME to A3’s referencing records, while deleting non-matching records in A1
|
|
5 |
=demo.cursor("select EID,NAME,DEPT from employee") |
Same as A1 |
|
6 |
=A5.fjoin@i((EID=A2.pfind(EID),NAME=A3.pfind(NAME),EID!=null&&NAME!=null)) |
A5 associates with A2 through foreign key field EID and with A3 through foreign key field NAME, by assigning ordinal numbers of the corresponding key values in A2 to A5’s foreign key field EID and assigning ordinal numbers of the corresponding key values in A3 to its foreign key field NAME, while deleting non-matching records from A5
|
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 |
=A1.fjoin(age(BIRTHDAY):age,age>50:ifRetire) |
Get age of each employee in cursor A1 – use age as the alias of age computation, and then find if each age is above 50, and add results of computing expression age>50 to cursor A1 as a new field named ifRetire; below is data of the returned cursor:
|
|
3 |
=A1.fjoin(age(BIRTHDAY),~:AGE) |
The parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY); below is data of the returned cursor:
|
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER,BIRTHDAY from employee") |
Return a cursor; below is the data:
|
|
2 |
=A1.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
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 cursor, the function won’t generate a new field but assigns new values to cursor A1’s GENDER field. below is data of the returned cursor:
|
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor
|
|
2 |
=A1.fjoin@i(CID<5) |
Use @i option to get records where CID is less than 5 from cursor A1, during which whole records will be deleted if result of the expression is null or false; below is data of the returned cursor: |
ch.fjoin ()
Description:
Perform foreign-key-style association on a channel.
Syntax:
ch.fjoin(w:T,x:F,…;…)
Note:
The function computes expression w over each row of channel ch and then expression x on w to generate a new field F, and returns a channel consisting of ch and F.
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.
The function attaches an operation to another 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 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
|
|
2 |
=channel() |
Create a channel |
|
3 |
=A1.push(A2) |
Push cursor A1’s data into the channel |
|
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)) |
Execute the computation in channel A2; use syntax k=w to associate channel A2 with table sequence A4 through the former ’s foreign key STATEID, and return a result set by assigning corresponding referencing records in A4 to A2’s foreign key and nulls to the non-matching records; view the result in A8 |
|
6 |
=A2.fetch() |
Attach a function that returns the final result set to channel A2 while keeping the existing data in the channel |
|
7 |
=A1.fetch() |
|
|
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
|
|
2 |
=channel() |
Create a channel |
|
3 |
=A1.push(A2) |
Push cursor A1’s data into the channel |
|
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)) |
Execute the computation in channel A2; 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; view the result in A8 |
|
6 |
=A2.fetch() |
Attach a function that returns the final result set to channel A2 while keeping the existing data in the channel |
|
7 |
=A1.fetch() |
|
|
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
|
|
2 |
=channel() |
Create a channel |
|
3 |
=channel() |
Create a channel |
|
4 |
=A1.push(A2,A3) |
Push cursor A1’s data into channel A2 and channel A3 |
|
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)) |
Execute the computation in channel A2; 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; view the computing result in A12 |
|
8 |
=A2.fetch() |
Attach a function that returns the final result set to channel A2 while keeping the existing data in the channel |
|
9 |
=A3.fjoin@i((EID=A5.pfind(EID),NAME=A6.pfind(NAME),EID!=null&&NAME!=null)) |
Execute the computation in channel A3; 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; view the computing result in A13 |
|
10 |
=A3.fetch() |
Attach a function that returns the final result set to channel A3 while keeping the existing data in the channel |
|
11 |
=A1.fetch() |
|
|
12 |
=A2.result() |
Retrieve channle A2’s result
|
|
13 |
=A3.result() |
Retrieve channle 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) |
Push cursor A1’s data into the channel |
|
5 |
=A2.fjoin(age(BIRTHDAY):age,age>50:ifRetire) |
Execute the computation in channel A2; get age of each employee in cursor A1 – use age as the alias of age computation, and then find if each age is above 50, and add results of computing expression age>50 to A1 as a new field named ifRetire; view computing result in A10 |
|
6 |
=A2.fetch() |
Attach a function that returns the final result set to channel A2 while keeping the existing data in the channel |
|
7 |
=A3.fjoin(age(BIRTHDAY),~:AGE) |
Parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY); view computing result in A11 |
|
8 |
=A3.fetch() |
Attach a function that returns the final result set to channel A3 while keeping the existing data in the channel |
|
9 |
=A1.fetch() |
|
|
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) |
Push cursor A1’s data into the channel |
|
4 |
=A2.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
Execute the computation in channel A2; 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; view computing result in A7 |
|
5 |
=A2.fetch() |
Attach a function that returns the final result set to channel A2 while keeping the existing data in the channel |
|
6 |
=A1.fetch() |
|
|
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) |
Push cursor A1’s data into the channel |
|
4 |
=A2.fjoin@i(CID<5) |
Execute the computation in channel A2; 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; view computing result in A7 |
|
5 |
=A2.fetch() |
Attach a function that returns the final result set to channel A2 while keeping the existing data in the channel |
|
6 |
=A1.fetch() |
|
|
7 |
=A2.result() |
Retrieve channle A2’s result
|
T.fjoin ()
Description:
Perform foreign-key-style association on a pseudo table.
Sytnax:
T.fjoin(w:Ti,x:F,…;…)
Note:
The function computes expression w over each row of pseudo table T and then expression x on w to generate a new field F, and returns a pseudo table consisting of T and F.
Ti 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 pseudo table sequence T.
A primary table and a subtable can be associated through the foreign key in parameter w.
Parameter:
|
T |
A pseudo table |
|
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 pseudo table T, and you can use an esProc function in w; 2. (Ki=wi,…,w), which contains the combined use of Ki=wi and where Ki can be referenced in w |
|
Ti |
Alias of expression w; can be absent |
|
x |
An expression; can be absent |
|
F |
Field name in expression x; can be absent |
Return value:
Pseudo table
Option:
|
@i |
Delete the current record when result of expression w is null or false |
Example:
When using syntax K=w:
|
|
A |
|
|
1 |
=create(file).record(["cities-x.ctx"]) |
Below is content of composite table cities-x.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose primary key is STATEID
|
|
4 |
=A2.fjoin(STATEID=A3.find(STATEID)) |
Use the syntax k=w to associate table sequence A3 and pseudo table A2 through the latter’s foreign key STATEID, and return a pseudo table by assigning corresponding referencing records in A3 to A2’s foreign key and nulls to the non-matching records. Below is content of the returned pseudo table:
|
When @i option is present:
|
|
A |
|
|
1 |
=create(file).record(["cities-x.ctx"]) |
Below is content of composite table cities-x.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose primary key is STATEID
|
|
4 |
=A2.fjoin@i(STATEID=A3.find(STATEID)) |
Use @i option to associate table A3 and A2 through the latter’s foreign key STATEID, during which whole records of A2 will be deleted if the foreign key value cannot find a match in A3; below is content of the returned pseudo table:
|
When using syntax (Ki=wi,…,w):
|
|
A |
|
|
1 |
=create(file).record(["emp-fj.ctx"]) |
Below is content of composite table emp-fj.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=6.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence whose primary key is ID
|
|
4 |
=create(name,gender).record(["Rebecca","F","Ashley","F","Matthew","M"]).keys(name) |
Generate a table sequence whose primary key is name
|
|
5 |
=A2.fjoin@i((EID=A3.find(EID),NAME=A4.find(NAME),EID!=null&&NAME!=null)) |
Pseudo table A2 associates with A3 through foreign key field EID and with A4 through foreign key field NAME by switching EID to A3’s referencing records and NAME to A4’s referencing records, while deleting non-matching records in A2
|
|
6 |
=A2.fjoin@i((EID=A3.pfind(EID),NAME=A4.pfind(NAME),EID!=null&&NAME!=null)) |
Pseudo table A2 associates with A3 through foreign key field EID and with A4 through foreign key field NAME, by assigning ordinal numbers of the corresponding key values in A3 to A2’s foreign key field EID and assigning ordinal numbers of the corresponding key values in A4 to A2’s foreign key field NAME, while deleting non-matching records from A2
|
When using other syntax:
|
|
A |
|
|
1 |
=create(file).record(["emp-fj2.ctx"]) |
Below is content of composite table emp-fj2.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=A2.fjoin(age(BIRTHDAY):age,age>50:ifRetire) |
Get age of each employee in pseudo table A2 – use age as the alias of age computation, and then find if each age is above 50, and add results of computing expression age>50 to pseudo table A2 as a new field named ifRetire; below is content of the returned pseudo table:
|
|
4 |
=A2.fjoin(age(BIRTHDAY),~:AGE) |
The parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY); below is content of the returned pseudo table:
|
|
|
A |
|
|
1 |
=create(file).record(["emp-fj2.ctx"]) |
Below is content of composite table emp-fj2.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=A2.fjoin(if(GENDER=="F","female","male"):GENDER,GENDER) |
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 pseudo table, the function won’t generate a new field but assigns new values to the GENDER field; below is content of the returned pseudo table:
|
|
|
A |
|
|
1 |
=create(file).record(["cities-x.ctx"]) |
Below is content of composite table cities-x.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=A2.fjoin@i(CID<5) |
Use @i option to get records of pseudo table where CID is less than 5 while deleting the whole record when computing result is null or false; below is content of the returned pseudo table: |