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.

Next page: Resources

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.