fjoin()

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. (K_i=w_i,…,w), which contains the combined use of K_i=w_i, where w is a logical expression, and where K_i 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

@i

Delete the current record when result of expression w is null or false

@m

Enable parallel processing

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.

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.

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.

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:

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

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 K_i=w_i, where w is a logical value and where K_i 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

@i

Delete the current record when result of expression w is null or false

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:

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:

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 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, and return the original cursor; below is data in cursor A5 after the attached computation is executed:

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:

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. (K_i=w_i,…,w), which contains the combined use of K_i=w_i, where w is a logical expression, and where K_i 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

@i

Delete the current record when result of expression w is null or false

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.

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.

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.

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.

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. (K_i=w_i,…,w), which contains the combined use of K_i=w_i, where w is a logical expression, and where K_i 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

@i

Delete the current record when result of expression w is null or false

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))

Define a computation on A2’s pseudo table, which will use syntax k=w to associate table sequence A3 and pseudo table A2 through the latter’s foreign key STATEID, assign corresponding referencing records in A3 to A2’s foreign key and nulls to the non-matching records, and return a new pseudo table:

5

=A4.import()

Import data from A4’s pseudo table while the computation defined on A2’s pseudo table in A4 is executed, and return the following 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

=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))

Define a computation on A2’s pseudo table, which will use @i option to associate table A3 and A2 through the latter’s foreign key STATEID, where whole records of A2 will be deleted if the foreign key value cannot find a match in A3, and return a new pseudo table.

5

=A4.cursor().fetch()

Import data from A4’s pseudo table while the computation defined on A2’s pseudo table in A4 is executed, and return the following pseudo table:

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))

Define a computation on A2’s pseudo table, which will associate pseudo table A2 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, and retrun a new pseudo table.

6

=A5.cursor().fetch()

Fetch data from A5’s pseudo table while the computation defined on A2’s pseudo table in A5 is executed, and return the following pseudo table:

7

=A2.fjoin@i((EID=A3.pfind(EID),NAME=A4.pfind(NAME),EID!=null&&NAME!=null))

Define a computation on A2’s pseudo table , which will associate pseudo table A2 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, and return a new pseudo table.

8

=A7.import()

Import data from A7’s pseudo table while the computation defined on A2’s pseudo table in A7 is executed, and return the following 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(age(BIRTHDAY):age,age>50:ifRetire)

Define a computation on A2’s pseudo table, which will get age of each employee in pseudo table A2 – use age as the alias of age computation, 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, and return a new pseudo table:

4

=A3.cursor().fetch()

Fetch data from A3’s pseudo table while the computation defined on A2’s pseudo table in A3 is executed, and return the following pseudo table:

5

=A2.fjoin(age(BIRTHDAY),~:AGE)

Define a computation on A2’s pseudo table, where parameter x represented by ~ means parameter w itself, that is, AGE field contains results of computing age(BIRTHDAY), and return a new pseudo table.

6

=A5.import()

Import data from A5’s pseudo table while executing the computation defined in A5 on A2’s pseudo table, and return the following 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)

Define a computation on A2’s pseudo table – 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 – and return a new pseudo table.

4

=A3.import()

Import data from A3’s pseudo table while executing the computation defined in A3 on A2’s pseudo table, and return the following 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)

Define a computation on A2’s pseudo table, which will 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.

4

=A3.import()

Import data from A3’s pseudo table while executing the computation defined in A3 on A2’s pseudo table, and return the following pseudo table: