Quick links:
This section describes expressions that allow you to join data from several Registers, Catalogs, or temporary data sources.
Actions with tables are based on relational algebra (see more Wiki > Relational algebra )
The main actions with tables include:
- JOIN. In the case when the source tables are of different structure, and it is necessary to supplement the source table with data that is not in it, table Joins are used.
- UNION. In case there are two or more tables of the same structure. It is necessary to compose one result table from them (Union into one table).
We’ll try to explain the essence of these operations in easy and understandable terms, see more Data join ( basics ).
LeftJoin
The function performs a left outer join. All rows of the main table are included, and rows of the joined table that satisfy the join condition.
Format:
LeftJoin ( tempTable, fieldName1 as alias1,…,fieldNameN as aliasN, joinCondition)
Parameters:
tempTable – the name of the temporary table to be joined, for example, $result_1, $result_2
fieldName1,…, fieldNameN – The names of the resulting fields. Fields included from the main table are indicated simply by name. The fields of the joined table are entered with the name of the temporary table, for example, result_1.product, result_1.amount
alias1,…, aliasN – if necessary, you can set field aliases.
joinCondition – join conditions, for example, result_1.product = product. In conditions, you can use “And” or “Or” for compound conditions.
Data type returned:
table
Description:
The result of the join is a table in which only rows of both joined tables that satisfy the join condition are included.
Example:
Employees | Last (Employee, Rate);
Timesheet | GroupBy (Employee, Hours) | LeftJoin ($result_1, Employee, Hours, result_1.Rate as Rate, Employee = result_1.Employee) | Compute (Payroll, Rate * Hours )
Comments of example. We select employee rates from the Employees Register, put it in the temporary table – $result_1. Then, we select the worked hours of employees from the Timesheet Register. Perform a left join by the matching condition in the employee line. Then, we calculate Payroll.
LeftJoinAuto
Simplified Left Join syntax. The function performs a left outer join. All rows of the main table are included, and rows of the joined table that satisfy the join condition.
Format:
LeftJoinAuto ( tempTable, joinCondition)
Parameters:
tempTable – the name of the temporary table to be joined, for example, $result_1, $result_2
joinCondition – join conditions, for example, result_1.product = product. In conditions, you can use “And” or “Or” for compound conditions.
Data type returned:
table
Description:
The result of the join is a table in which only rows of both joined tables that satisfy the join condition are included.
As parameters, you must specify only the name of the joined table and the join condition.
The composition of the columns of the resulting table will be generated automatically according to the following algorithm: all columns of the main table, as well as columns of the joined table, whose names differ from the names of the columns of the joined table, will be included in the resulting table.
The simplified syntax is applicable in most cases encountered in practice. But for the successful application of the simplified syntax, it is necessary to prepare the composition of the columns in the source tables before joining: set the required aliases, delete the extra columns, select the ones you need.
If the desired result cannot be achieved using the simplified syntax, the full version of the syntax should be used.
Example:
Employees | Last (Employee, Rate);
Timesheet | GroupBy (Employee, Hours) | LeftJoinAuto ($result_1, Employee = result_1.Employee) | Compute (Payroll, Rate * Hours )
InnerJoin
The function performs an internal join. Only rows in both tables that satisfy the join condition are included in the result.
Format:
InnerJoin ( tempTable, fieldName1 as alias1,…,fieldNameN as aliasN, joinCondition)
Parameters:
tempTable – the name of the temporary table to be joined, for example, $result_1, $result_2
fieldName1,…, fieldNameN – The names of the resulting fields. Fields included from the main table are indicated simply by name. The fields of the joined table are entered with the name of the temporary table, for example, result_1.product, result_1.amount
alias1,…, aliasN – if necessary, you can set field aliases.
joinCondition – join conditions, for example, result_1.product = product. In conditions, you can use “And” or “Or” for compound conditions.
Data type returned:
table
Description:
The result of the join is a table in which only rows of both joined tables that satisfy the join condition are included.
Example:
Employees | Last (Employee, Rate);
Timesheet | GroupBy (Employee, Hours) | InnerJoin ($result_1, Employee, Hours, result_1.Rate as Rate, Employee = result_1.Employee) | Compute (Payroll, Rate * Hours )
Comments of example. We select employee rates from the Employees Register, put it in the temporary table – $result_1. Then, we select the worked hours of employees from the Timesheet Register. Perform an Inner Join by the matching condition in the employee line. Then, we calculate Payroll.
FullJoin
The function performs a full outer join.
Format:
FullJoin ( tempTable, fieldName1 as alias1,…,fieldNameN as aliasN, joinCondition)
Parameters:
tempTable – the name of the temporary table to be joined, for example, $result_1, $result_2
fieldName1,…, fieldNameN – The names of the resulting fields. Fields included from the main table are indicated simply by name. The fields of the joined table are entered with the name of the temporary table, for example, result_1.product, result_1.amount
alias1,…, aliasN – if necessary, you can set field aliases.
joinCondition – join conditions, for example, result_1.product = product. In conditions, you can use “And” or “Or” for compound conditions.
Data type returned:
table
Description:
The result of a full outer join is a table that includes rows that are the result of an inner join. Plus, rows of the first table that do not satisfy the join condition; in these rows, the values in the columns of the second table are filled with null. And the rows of the second table that do not satisfy the join condition, in these row values in the columns of the first table are filled with null values.
Example:
Prices | Product ($r.product) | Last( currency ) | Value ( currency );
Currency_rates | currency( $result_1) | Last( currency, rate );
Currency_rates | currency( $h.currency_custom ) | Last( currency, rate) ;
Prices | Product ($r.product) | Last(currency, amount) | FullJoin ( $result_2, result_2.rate as rate_price) | FullJoin( $result_3, result_3.rate as rate_custom) | Compute (price, amount * rate_price / rate_custom ) | Value (price)
Comments of example. In the presented example, the product price is calculated depending on the chosen transaction currency.
First line. we get the currency in which the current value of the product is set.
The second line. Get the ratio of the current currency.
Third line. We get the ratio of the selected currency.
Fourth line. We get the current price of the selected product, Join the previously obtained data, and perform the calculation.
FullJoinAuto
Simplified Full Join syntax. The function performs a full outer join.
Format:
FullJoinAuto ( tempTable, joinCondition)
Parameters:
tempTable – the name of the temporary table to be joined, for example, $result_1, $result_2
joinCondition – join conditions, for example, result_1.product = product. In conditions, you can use “And” or “Or” for compound conditions. Used when necessary
Data type returned:
table
Description:
The result of a full outer join is a table that includes rows that are the result of an inner join. Plus, rows of the first table that do not satisfy the join condition; in these rows, the values in the columns of the second table are filled with null. And the rows of the second table that do not satisfy the join condition, in these row values in the columns of the first table are filled with null values.
As parameters, you must specify only the name of the joined table and the join condition. The composition of the columns of the resulting table will be generated automatically according to the following algorithm: all columns of the main table, as well as columns of the joined table, whose names differ from the names of the columns of the main table, will be included in the resulting table.
In the rows of the resulting table that were created from the rows of the joined table for which there were no matches in the source table, the column values from the first table will be filled with the values from the second table, provided that the names match.
When using FullJoinAuto, we save on the “collapse” of the same meaning columns into one.
The simplified syntax is applicable in most cases encountered in practice. But for the successful application of the simplified syntax, it is necessary to prepare the composition of the columns in the source tables before joining: set the required aliases, delete the extra columns, select the ones you need.
If the desired result cannot be achieved using the simplified syntax, the full version of the syntax should be used.
Example:
Prices | Product ($r.product) | Last( currency ) | Value ( currency );
Currency_rates | currency( $result_1) | Last( currency, rate as rate_price );
Currency_rates | currency( $h.currency_custom ) | Last( currency, rate as rate_custom);
Prices | Product ($r.product) | Last(currency, amount) | FullJoinAuto ( $result_2, ) | FullJoinAuto ( $result_3, ) | Compute (price, amount * rate_price / rate_custom ) | Value (price)
Comments of example. In the presented example, the product price is calculated depending on the chosen transaction currency.
First line. we get the currency in which the current value of the product is set.
The second line. Get the ratio of the current currency.
Third line. We get the ratio of the selected currency.
Fourth line. We get the current price of the selected product, Join the previously obtained data, and perform the calculation.
UnionAll
Join two or more tables. Rows of joined tables are “add” to the bottom. The list of columns is determined by the main table.
Format:
UnionAll ( tempTable1, … , tempTableN )
Parameters:
tempTable1, … ,tempTableN – the names of the temporary tables to be joined, for example, $result_1, $result_2.
Data type returned:
table
Example:
Reserves | GroupBy (Product, Quantity);
Inventory | GroupBy (Product, Quantity, Amount) | UnionAll ($ result_1)
All rows in the Reserves table will be added to the Inventory table.
Union
Join two or more tables. Records of joined tables are “add” below. The list of columns is determined by the main table.
Only unique rows are added.
To correctly check the uniqueness of rows, it is necessary that the columns in the joined tables match.
Format:
Union ( tempTable1, … , tempTableN )
Parameters:
tempTable1, … ,tempTableN – the names of the temporary tables to be joined, for example, $result_1, $result_2.
Data type returned:
table
Example:
Reserves | GroupBy (Product, Quantity);
Inventory | GroupBy (Product, Quantity) | Union ($ result_1)
All rows in the Reserves table will be added to the Inventory table.
VLookUp
The function searches for data in another Register / Catalog and attaches the specified fields to the current table.
Format:
VLookUp( dataField, searchObject, searchField, fieldName1 as alias1, … , fieldNameN as aliasN )
Parameters:
dataField – field of current data, which will be searched
searchObject – Register / Catalog in which the search is performed
searchField – the search field to be searched
fieldName1, … , fieldNameN – the names of the fields to be selected from the Register / Catalog
alias1, … , aliasN – field aliases
Data type returned:
table
Description:
A function is not an operation of relational algebra. This function is easier to understand than table join operations.
The VLookUp function can replace the left join when the join condition is the simple equality of the two fields in the tables.
If necessary, several VLookUp functions can be used within a single expression.
Example:
Timesheet | GroupBy (Employee, Hours) | VLookUp (Employees, Employee, Employee, Rate) | Compute (Payroll, Rate * Hours )