Analyzing query text
Let us analyze the generated query text (listing 13.6).
Listing 13.6. Query text
SELECT MaterialsAndServicesCat.Ref AS Service, SalesTurnovers.RevenueTurnover AS Revenue FROM Catalog.MaterialsAndServices AS MaterialsAndServicesCat LEFT JOIN AccumulationRegister.Sales.Turnovers AS SalesTurnovers ON SalesTurnovers.MaterialOrService = MaterialsAndServicesCat.Ref WHERE MaterialsAndServicesCat.IsFolder = FALSE AND MaterialsAndServicesCat.MaterialServiceType = &MaterialServiceType ORDER BY Revenue DESC
The query begins with a usual query description part, this time it contains statements you have not yet encountered.
The query source description (after the FROM keyword) specifies multiple sources (listing 13.7).
Listing 13.7 Multiple query sources description
FROM Catalog.MaterialsAndServices AS MaterialsAndServicesCat LEFT JOIN AccumulationRegister.Sales.Turnovers AS SalesTurnovers ON SalesTurnovers.MaterialOrService = MaterialsAndServicesCat.Ref
In this case records are selected from two sources (MaterialsAndServicesCat and SalesTurnover), while the LEFT JOIN … ON keywords describe how the records from these two sources are combined.
LEFT JOIN means that the query result includes a combination of records from both sources that satisfy the condition stated after the ON keyword. Additionally, the query result includes records from the first source (which is specified to the left of the JOIN keyword) that are not linked to any records that match the condition in the second source.
Let us review the next lines of the query text. In the query definition section there is one more statement that is new to you: specifying the filter conditions for source table data (listing 13.8).
Listing 13.8. Filter conditions
WHERE MaterialsAndServicesCat.IsFolder = FALSE AND MaterialsAndServicesCat.MaterialServiceType = &MaterialServiceType
A filter condition is always preceded by the WHERE keyword. The condition itself follows the keyword. Note that the source table fields that the condition is applied to do not have to be present in the selection list (this is the case here). And the condition includes the MaterialServiceType query parameter preceded by ampersand (&).
Now that we are done reviewing the query text, let us proceed to data composition schema generation.