Base Transaction clause

Official Content
This documentation is valid for:

Allows indicating the Transaction Level from which the base table will be determined.

Although optional, improves the expressive capacity of the command or control to which it is associated, allowing the developer to declare its intention in a more intuitive manner. In addition, time for specification is improved, because it isn't needed to do the calculation of the command/statement or control Base Table.

It may be a list of Transaction levels. In that case, a kind of 'product' between the tables is done, solving queries which imply navigation over different tables that are difficult to express in another way, or that cannot be solved in a single SQL sentence.

Syntax

<BaseTrnList> ::=  <Trn1> {, <Trni>}...

where:

<Trni> is a Transaction Level name.

Description

If one base transaction is declared, the table associated with this transaction level is considered as the Base Table of the context that clause is linked (e.g. the For Each command), and all attribute belonging to that context (e.g. for each body, conditions, orders, etc.) have to be in its extended. Otherwise, the navigation report will throw a warning 'Attribute is not instantiated' for all attributes not belonging to the extended base table.

If more than one base transaction is declared and the tables are directly related (or you define an explicit filter by equality between two different attributes), then a join is made among the tables associated to these transaction levels. Otherwise, a cartesian product is done, or if no relation can be found, then a specification error will occur (spc0027).

The attributes of the context this clause is linked to, have to be in the extended of any of these tables.

Thus, in the following example: Multiple base TRN example - simple

As long as there is a relationship between A and C (by means of B), the For Each command will consider:

for each A record, all C related by means of the corresponding B (that is, a join will be made)

Notes:

  • It will be an inner join unless the corresponding foreign key is nullable. In such a case an outer join is selected. See Join Type and Join Location Specification for more information on joins.
  • In any other case (the base TRNs are related but not directly through an N-1 relation with another table, or the base TRNs are not related at all, and you have not defined an explicit filter by equality between attributes) for each record in TRN1 all records in TRN2 will be considered (Cartesian product).

To see further examples: Multiple Base Transactions in a For Each command.

Examples

Having the following transactions:

User            //Transaction 1st. level name
{
   UserId*
   UserName
   UserType
   Role        //Transaction 2nd. level name
   {
      RoleId*
   }
}

Role            //Transaction 1st. level name
{
   RoleId*
   RoleName
}

For each example

If you want to display all the users by means of a procedure, with a For Each command using base transaction clause:

for each User
   print userInfo  //printblock with UserId, UserName attributes
endfor

Note the 'User' transaction level is specified, thus, the for each base table will be associated with that level: USER. Thus, the attributes inside the body of the For each (as well as orders, conditions, etc.) have to be in the extended table of USER (and they do!). 

Data provider Group example

An equivalent example would be writing the following Data Provider group definition:

UserCollection User
{
   UserItem
   {
        Id = UserId
        Name = UserName
   }
}

The Data provider will return a collection SDT as output, loaded through scanning the USER table.

Example: for each over m-n relation

Suppose you want to display, for each role, the related users. Then:

for each Role
   print roleInfo             //print block with RoleId, RoleName
   for each User.Role
      print userInfo         //print block with UserId, UserName
   endfor
endfor

Note that the inner for each base table will be that associated with the second level of 'User' transaction (named User.Role). That's why the users displayed for each role are the corresponding ones (a join is made). 

Example: cartesian product with multiple base transactions

Having a Program transaction, which stores the information of each program of our system, and that is not related to Users and Roles transactions at all, suppose you need to list the Cartesian product of the User and Program tables filtering by the administrator user (due to the administrator has access to all programs). 

The code would be as follows:

For each User, Program
    where UserType = 'Administrator'
   print crossInfo     //print block with UserName, ProgramName attributes
endfor

Note there is no relationship between USER table and PROGRAM table through any table. Thus, for each user of 'administrator' type, all program names will be displayed next to the user name.

Examples: Join with multiple base transactions

1) Consider a multi-company system where you know the products sold by each Company and distributed by each Provider. The transaction structures would be as follows:

Product
{
   ProductId*
   ProductName
}

Company
{
   CompanyId*
   CompanyName
   Product
   {
      ProductId*
      ProductName
   }
}

Provider
{
   ProviderId*
   ProviderName
   Product
   {
      ProductId
      ProductName
   }
}

In that scenario, suppose you need to list the combination of Companies and Providers which sell the same given product:

for each Company.Product, Provider.Product
   where ProductId = &productId
   print crossInfo    //printblock with CompanyName, ProviderName attributes
endfor

Note the tables COMPANYPRODUCT and PROVIDERPRODUCT are directly related by means of PRODUCT table. Thus, for each record in Company.Product with ProductId = &productId, all the related records on PROVIDERPRODUCT will be considered (all records matching: PROVIDERPRODUCT. ProductId = COMPANYPRODUCT.ProductId). 

 

2) Consider the following transaction structures:

Transaction1
{
  A*
  B  
  C  
}

Transaction2
{
  D*
  E
}

In that scenario, where there is no relationship between B and D (their names are different and they are not subtypes), if you define a For each like the following:

For each Transaction1 ,Transaction2
  where B = D
   print printblock1 //printblock with C and E attributes
endfor

where Transaction1 and Transaction2 are declared as base transactions and an explicit filter by equality between B and D attributes is defined, then a join is made among the tables associated to Transaction1 and Transaction2.

In this particular example, the SQL sentence (for SQL Server) is generated as shown below:

SELECT T2.[D], T1.[B], T1.[C], T2.[E], T1.[A] FROM [Transaction1] T1,  [Transaction2] T2 WHERE T1.[B] = T2.[D] ORDER BY T1.[A].

Scope

Commands  For Each commandData provider Group statement



Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.