support
buy cialis buy valium online buy viagra online

Code Examples of esProc Foreign Key Function

If k (a single field or a combination of fields) is table M’s primary key, and k, at the same time, exists in table B, then k is regarded as B’s foreign key. The foreign key defines a relationship between two tables and is one of the most important concepts for structured data computing. Through object references, esProc makes foreign key easy to function. The following examples aim to illustrate the how foreign key is used in esProc.

Example 1 Associate a referenced table and a referencing table

Order (containing orders information) is the referencing table, emp(containing employee information) is the referenced table. It is required to connect emp with order and display emp’s Name field, Gender field and Salary field and order’s OrderID field and Amount field in the result table.

Note: Besides emp table and order table used here, dep table (containing departments information) is to be used in subsequent examples. The relationship between emp and order/dep through the foreign key is shown as follows:

esProc_inmemory_foreign_key_1

Data may originate from databases or text files. For example:

         order=esProc.query(“select OrderID,SellerId,Amount,OrderDate from sales”)

         emp=esProc.query(“select EId,Name,Gender,Birthday,Dept,Salary from emp”)

         dep=esProc.query(“select * from department”)

esProc code for doing this:

         A3=order.switch(SellerId, emp:EId)

         A4=order.new(OrderID,Amount,SellerId.Name,SellerId.Gender,SellerId.Salary)

Computed result:

esProc_inmemory_foreign_key_2

Code explanation:

A3: Replace records of order’s SellerID with their corresponding ones in emp to create a foreign key relationship between the two tables.

A4: Get OrderID field and Amount field from order, and get Name, Gender and Salary field from emp through foreign key references. We can see that, with object references, fields in emp can be accessed directly from order, thus saving us the trouble of writing complex and difficult join statements.

Example 2: Query referencing table according to condition existing in referenced table

Find orders signed by female sellers whose salary is greater than 10,000.

esProc code for doing this:

         A3=order.switch(SellerId, emp:EId)         / the same as above example

         A5=order.select(SellerId.Salary>10000 && SellerId.Gender==”F”)

Computed results:

esProc_inmemory_foreign_key_3

Click the above hyperlinks in blue and corresponding employee information will be shown:

esProc_inmemory_foreign_key_4

Example 3: Group data according to referenced table

Compute sales amount of each department.

esProc code for doing this:

         A3=order.switch(SellerId, emp:EId)         / the same as above example

         A5=order.groups(SellerId.Dept;sum(Amount))

Computed results:

esProc_inmemory_foreign_key_5

You can rename fields, like order.groups(SellerId.Dept:dt;sum(Amount):amt). The effect of name changing is shown below:

esProc_inmemory_foreign_key_6

Example 4: Complex association between multiple tables

Find managers of departments whose sales amount is greater than 50,000. 

esProc code for doing this:

         A3=order.switch(SellerId, emp:EId)        

         A4=dep.switch(Manager,emp:EId)

         A5=emp.switch(Dept,dep:DeptNo)

         A6=order.groups(SellerId.Dept:dt;sum(Amount):amt)

         A7=A6.select(amt<=50000).(dt).(Manager).(Name)

Computed results:

esProc_inmemory_foreign_key_7

Code explanation:

A3, A4, A5:Create complete foreign key relationships.

A6: Compute sales amount of each department (See above example). The result is:

esProc_inmemory_foreign_key_8

A7:Use object references to solve the problem intuitionally. Expression A6.select(amt<=50000).(dt).(Manager).(Name) can be divided into four steps according to full stops. They are:

1. Find records whose sales amount is greater than 50,000 from A6.

2. Get records corresponding to those in dt field (from dep table)

3. Get records corresponding to those in Manager field (from emp table)

4. Get Name field.

Details are as follows:

A6.select(amt<=50000)

esProc_inmemory_foreign_key_9

.(dt)

esProc_inmemory_foreign_key_10

.(Manager)

esProc_inmemory_foreign_key_11

.(Name)

esProc_inmemory_foreign_key_12

 

replica franck-muller watches  replica hublot watches  replica panerai watches  replica bvlgari watches  replica patek-philippe watches  replica montblanc watches  replica longines watches  replica hermes watches  replica audemars piguet watches  replica vacheron constantin watches