MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Referencing Thoughts in esProc

Published on 10 July 14
278
0
0

1 Foreign key referencing

The foreign key is a common concept for various relational databases. In a relational table, you can define one or multiple fields as the foreign key. The foreign key field values can be associated with the data in another table. For example, in the game scoring report, player scores are usually recorded with the player ID. To get the details of this player from another player profile table using the player ID, you can query in the player profile table according to the player ID. In this case, the player ID field can be set as the foreign key.

The foreign key can associate two relational tables and help ensure the data consistency, so as to facilitate some cascade operations. Take the above game scoring report for example. If defining the player ID as the foreign key field, then the player name, country, age, and other data can be queried at the same time while getting the game scoring record of the player.

In this example of foreign key definition, it is actually equal to referencing the records n the player data table from the game scoring record table. The core idea of foreign key concept is to reference another record.

In esProc, unlike the database, there is no particular requirement on the data type of record fields. In other words, esProc users can get the value at will. Thanks to this convenience, in the esProc TSeq, fields can be directly assigned with record for referencing. The foreign key associating can thus be established naturally and straightforwardly.

For example, create an employee education background table comprising the employee ID field and education field. This example is designed to illustrate the way to reference the esProc foreign key, and the education field is simply set through the looping in proper order:
Referencing Thoughts in esProc - Image 1
At this point, associate this TSeq with employee table EMPLOYEE. A foreign key field Info can be added to the education table so as to reference the records in the table EMPLOYEE with the foreign key field value:
Referencing Thoughts in esProc - Image 2
To this point, the TSeq in B4 is as follows. In this TSeq, the Info field is the employee record on which you can double-click to query.
Referencing Thoughts in esProc - Image 3
With the record reference, the foreign-key-related query becomes more convenient to implement. The query statement gets much more clear and intuitive. To this point, the record fields can be used in the same way of using the normal fields or records. For example, list the data of serial number, full name, education background, state, and department:
Referencing Thoughts in esProc - Image 4

In esProc, not only the record of other TSeq but also that of the current TSeq can be assigned to the foreign key field.

In esProc, besides the function A.derive() for assign a certain field of record with the objects of another entire record when adding fields to the newly-generated TSeq; the function A.run() can be used to assign value to the existing fields. For example:
Referencing Thoughts in esProc - Image 5
In B4, modify the employee serial number field in the Education Background TSeq to the corresponding records. In A1, the TSeq is as follows:
Referencing Thoughts in esProc - Image 6
If the primary key of the corresponding table is a single field, you can also use the function A.switch():
Referencing Thoughts in esProc - Image 7
The TSeq in A1 is the same to that in the previous example, but much more concise and clear. One thing to note is that the switch function can be used to have the primary keys of specified fields and TSeq associated automatically. If the primary key of TSeq is not the first field, you need to set it with the function T.primary(). For example, the STATE field of employee is associated with the STATE data table:
Referencing Thoughts in esProc - Image 8
In this case, the State information can be further viewed in the employee data of TSeq in A1:
Referencing Thoughts in esProc - Image 9
To this point, the query or computation for multi-table association can be further processed. For example, find the state, in which the employee is located, has a population of below 1,000,000:
Referencing Thoughts in esProc - Image 10

As can be seen, the referencing foreign key of records can make the query syntax of multi-table association more concise and readable with an improved computing speed.

2 Referencing set

In assignment, esProc fields can be assigned with records or sets in a similar way - referencing the set in the foreign key field. In most cases, what being referenced in the foreign key fields is the RSeq composed of records from other TSeqs. Please notice that the âForeign Key Fieldâ is not the same thing as the foreign key field in the database. It is much more like the query to reference the sub table data from the primary table with SQL.

Like the foreign key referencing, fields can be added when generating the TSeq. With the function derive, the foreign key field can be assigned with RSeq, for example:
Referencing Thoughts in esProc - Image 11
In A3, add foreign key field Employees to A1, and assign this field with the employees in this State, then return a new TSeq. By doing so, all values of Employees fields in the TSeq are RSeq, as shown below:
Referencing Thoughts in esProc - Image 12
For the foreign key field with referring sets, they can be called directly as the fields of TSeq. The values of foreign key fields can be taken as the set for direct use in the computation. For example, add the field Count and compute the respective total employees in each State:
Referencing Thoughts in esProc - Image 13
The total employees can be computed with the foreign keys, and the resulting TSeq is shown in A4:
Referencing Thoughts in esProc - Image 14
Like referencing the foreign key field of record, the sets or RSeq are referenced in the foreign key field. In addition, the expressions on foreign key fields can also be used to solve the multi-table-associated query, filtering, and other problems. For example, find the State in which there are more than 50 employees:
Referencing Thoughts in esProc - Image 15

By referencing sets in foreign key fields, the syntax of multi-table-associated querying can be more clear and intuitive. In the TSeq of A4, the sole purpose of introducing the field Count is to have a more clear view of the result. This field is not used when filtering records in A5.

When referencing the set in the TSeq field, the switch function cannot be used, but the function A.run() can be used to assign value to the existing fields. For example:
Referencing Thoughts in esProc - Image 16
In A3, assign the blank field Employees with the record of employees born in each month. Once executed, the resulting TSeq in A1 is as follows:
Referencing Thoughts in esProc - Image 17
In facts, the result of grouping the RSeq is just a sequence composed of sets. It is similar to the referencing fields of a single set. For example, if grouping by the birth month of employee, the result is:
Referencing Thoughts in esProc - Image 18
Like the result in the above example, the result of grouping can be further used in various computations as necessary:
Referencing Thoughts in esProc - Image 19
In A3, count the total numbers of employees who was born in each respective month. The oldest employee born in each month is listed in A4.
1 Foreign key referencing

The foreign key is a common concept for various relational databases. In a relational table, you can define one or multiple fields as the foreign key. The foreign key field values can be associated with the data in another table. For example, in the game scoring report, player scores are usually recorded with the player ID. To get the details of this player from another player profile table using the player ID, you can query in the player profile table according to the player ID. In this case, the player ID field can be set as the foreign key.

The foreign key can associate two relational tables and help ensure the data consistency, so as to facilitate some cascade operations. Take the above game scoring report for example. If defining the player ID as the foreign key field, then the player name, country, age, and other data can be queried at the same time while getting the game scoring record of the player.

In this example of foreign key definition, it is actually equal to referencing the records n the player data table from the game scoring record table. The core idea of foreign key concept is to reference another record.

In esProc, unlike the database, there is no particular requirement on the data type of record fields. In other words, esProc users can get the value at will. Thanks to this convenience, in the esProc TSeq, fields can be directly assigned with record for referencing. The foreign key associating can thus be established naturally and straightforwardly.

For example, create an employee education background table comprising the employee ID field and education field. This example is designed to illustrate the way to reference the esProc foreign key, and the education field is simply set through the looping in proper order:

Referencing Thoughts in esProc - Image 1

At this point, associate this TSeq with employee table EMPLOYEE. A foreign key field Info can be added to the education table so as to reference the records in the table EMPLOYEE with the foreign key field value:

Referencing Thoughts in esProc - Image 2

To this point, the TSeq in B4 is as follows. In this TSeq, the Info field is the employee record on which you can double-click to query.

Referencing Thoughts in esProc - Image 3

With the record reference, the foreign-key-related query becomes more convenient to implement. The query statement gets much more clear and intuitive. To this point, the record fields can be used in the same way of using the normal fields or records. For example, list the data of serial number, full name, education background, state, and department:

Referencing Thoughts in esProc - Image 4

In esProc, not only the record of other TSeq but also that of the current TSeq can be assigned to the foreign key field.

In esProc, besides the function A.derive() for assign a certain field of record with the objects of another entire record when adding fields to the newly-generated TSeq; the function A.run() can be used to assign value to the existing fields. For example:

Referencing Thoughts in esProc - Image 5

In B4, modify the employee serial number field in the Education Background TSeq to the corresponding records. In A1, the TSeq is as follows:

Referencing Thoughts in esProc - Image 6

If the primary key of the corresponding table is a single field, you can also use the function A.switch():

Referencing Thoughts in esProc - Image 7

The TSeq in A1 is the same to that in the previous example, but much more concise and clear. One thing to note is that the switch function can be used to have the primary keys of specified fields and TSeq associated automatically. If the primary key of TSeq is not the first field, you need to set it with the function T.primary(). For example, the STATE field of employee is associated with the STATE data table:

Referencing Thoughts in esProc - Image 8

In this case, the State information can be further viewed in the employee data of TSeq in A1:

Referencing Thoughts in esProc - Image 9

To this point, the query or computation for multi-table association can be further processed. For example, find the state, in which the employee is located, has a population of below 1,000,000:

Referencing Thoughts in esProc - Image 10

As can be seen, the referencing foreign key of records can make the query syntax of multi-table association more concise and readable with an improved computing speed.

2 Referencing set

In assignment, esProc fields can be assigned with records or sets in a similar way - referencing the set in the foreign key field. In most cases, what being referenced in the foreign key fields is the RSeq composed of records from other TSeqs. Please notice that the âForeign Key Fieldâ is not the same thing as the foreign key field in the database. It is much more like the query to reference the sub table data from the primary table with SQL.

Like the foreign key referencing, fields can be added when generating the TSeq. With the function derive, the foreign key field can be assigned with RSeq, for example:

Referencing Thoughts in esProc - Image 11

In A3, add foreign key field Employees to A1, and assign this field with the employees in this State, then return a new TSeq. By doing so, all values of Employees fields in the TSeq are RSeq, as shown below:

Referencing Thoughts in esProc - Image 12

For the foreign key field with referring sets, they can be called directly as the fields of TSeq. The values of foreign key fields can be taken as the set for direct use in the computation. For example, add the field Count and compute the respective total employees in each State:

Referencing Thoughts in esProc - Image 13

The total employees can be computed with the foreign keys, and the resulting TSeq is shown in A4:

Referencing Thoughts in esProc - Image 14

Like referencing the foreign key field of record, the sets or RSeq are referenced in the foreign key field. In addition, the expressions on foreign key fields can also be used to solve the multi-table-associated query, filtering, and other problems. For example, find the State in which there are more than 50 employees:

Referencing Thoughts in esProc - Image 15

By referencing sets in foreign key fields, the syntax of multi-table-associated querying can be more clear and intuitive. In the TSeq of A4, the sole purpose of introducing the field Count is to have a more clear view of the result. This field is not used when filtering records in A5.

When referencing the set in the TSeq field, the switch function cannot be used, but the function A.run() can be used to assign value to the existing fields. For example:

Referencing Thoughts in esProc - Image 16

In A3, assign the blank field Employees with the record of employees born in each month. Once executed, the resulting TSeq in A1 is as follows:

Referencing Thoughts in esProc - Image 17

In facts, the result of grouping the RSeq is just a sequence composed of sets. It is similar to the referencing fields of a single set. For example, if grouping by the birth month of employee, the result is:

Referencing Thoughts in esProc - Image 18

Like the result in the above example, the result of grouping can be further used in various computations as necessary:

Referencing Thoughts in esProc - Image 19

In A3, count the total numbers of employees who was born in each respective month. The oldest employee born in each month is listed in A4.

This blog is listed under Development & Implementations Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top