cs.fjoin ()
Description:
Attach foreign-key-style association computation to a cursor and return the original cursor.
Syntax:
cs.fjoin(w:T,x:F,…;…)
Note:
The function attaches a computation to a cursor cs, which computes expression w over each row of the cursor and then expression x, and adds results of expression x under new field F as values to the cursor , and returns the original cursor.
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.
This is a delayed function.
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, where w is a logical value 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)) |
Attach a computation to cursor A1, which will 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, and return the original cursor; below is data in cursor A1 after the attached computation is executed:
|
When @i option is present:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor; below is the content:
|
|
2 |
=connect("demo").query("SELECT top 20 * FROM STATECAPITAL").keys(STATEID) |
|
|
3 |
=A1.fjoin@i(STATEID=A2.find(STATEID)) |
Attach a computation to cursor A1, which will 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, and return the original cursor; below is data in cursor A1 after the attached computation is executed:
|
When using syntax (Ki=wi,…,w):
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT from employee") |
Return a cursor; below is the content:
|
|
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)) |
Attach a computation to cursor A1, which will associate 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, and return the original cursor; below is data in cursor A1 after the attached computation is executed:
|
|
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)) |
Attach a computation to cursor A5, which will associate with A2 through foreign key field EID and with A3 through foreign key field NAME, by assigning sequence numbers of the corresponding key values in A2 to A5’s foreign key field EID and assigning sequence numbers of the corresponding key values in A3 to its foreign key field NAME, while deleting non-matching records from A5, and return the original cursor; below is data in cursor A5 after the attached computation is executed:
|
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) |
Attach a computation to cursor A1, which will get age of each employee in cursor A1 – use age as the alias of age computation and find if each age is above 50 – and add results of computing expression age>50 to cursor A1 as a new field named ifRetire, and return the original cursor; below is data in cursor A1 after the attached computation is executed:
|
|
3 |
=A1.fjoin(age(BIRTHDAY),~:AGE) |
Attach a computation to cursor A1, where parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY) and return the original cursor; below is data in cursor A1 after the attached computation is executed:
|
|
|
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) |
Attach a computation to cursor A1, 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 cursor, the function won’t generate a new field but assigns new values to cursor A1’s GENDER field and return the original cursor; below is data in cursor A1 after the attached computation is executed:
|
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor:
|
|
2 |
=A1.fjoin@i(CID<5) |
Attach a computation to cursor A1, which will 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, and return the original cursor; below is data in cursor A1 after the attached computation is executed: |
