<< Prev   Next >>

Query language

• Selection of all values belonging to a grouping from a query result
• Display of reference fields
• Complementing query result by days belonging to a specified period
• Using filters in queries with virtual tables
• Using FOR UPDATE clause in the query language
• Using custom expressions in query language totals
• Sorting in nested queries
• Using the ISNULL() function
• Details on how to use the group inclusion operator
• Specifics of using the GROUP BY clause
• Specifics of working with virtual table of calculation register schedule data
• Specifics of working with the Presentation field and Presentation() function of the query language
• Specifics of connecting to the virtual balances table
• Specifics of sorting by reference fields
• Specifics of DATEDIFF function of the query language
• Specifics of executing SUBSTRING() function of the query language
• Specifics of using "IN HIERARCHY" clause in query language
• Accessing fields from other fields using . (dot )
• Sample queries for hierarchical catalog operations
• Bitness of results of expressions and aggregate functions in query language
• Calculating totals by balance fields
• Catalog field contents

Selection of all values belonging to a grouping from a query result

When you iterate through a query result, you may often need to get all grouping values within another grouping. For example, you might need this for displaying a pivot table in a report. You can implement this using the third parameter of the Choose() function of the QueryResultSelection object.

Let us consider an example. Suppose you need to generate a pivot table of product balances in multiple warehouses. The products should be displayed in rows while warehouses should be represented by columns. A query that gets the balances has the following format:

SELECT
    ItemAccountingBalance.Items AS Items,
    ItemAccountingBalance.Items.Presentation,
    ItemAccountingBalance.Warehouse AS Warehouse,
    ItemAccountingBalance.Warehouse.Presentation,
    ItemAccountingBalance.QuantityBalance AS QuantityBalance
FROM
    AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
TOTALS SUM(QuantityBalance) BY 
    OVERALL,
    Items,
    Warehouse

To iterate through products, use the selection from the query result:

ItemSelection = Result.Choose(QueryResultIteration.ByGroups, "Items");
While ItemSelection.Next() Do
    ItemsArea.Parameters.Fill(ItemSelection);
    SpreadsheetDoc.Put(ItemsArea);

To iterate through the products in all warehouses available in the query result, get a nested selection based on the product selection and specify All for the third parameter:

WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Warehouse", "All");
While WarehouseSelection.Next() Do
    WarehouseArea.Parameters.Fill(WarehouseSelection);
    Spreadsheet.Join(WarehouseArea);
EndDo;

If you need to generate multiple independent pivot tables for each grouping value, in the third selection parameter specify the grouping name, so that the query retrieves values belonging to the grouping.

Example:

WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Warehouse", "Company");

This example gets the values of all the warehouses available in the selected company.

Display of reference fields

When you display reference fields in a report (as well as in the situations where you need to display a presentation, such as displaying a value using the Message function), note that in order to display a value that is a reference, the application executes an additional query to get the field presentation. This reduces the performance. To avoid this, we recommend that you get the presentation field for the reference field right in the query and then display the presentation field in the report. And it is feasible to output the reference field itself to cell details, so that users can open the cell value to view the item.

Example:

SELECT
    CompanySalesTurnovers.Items AS Items, 
    CompanySalesTurnovers.SalesDocument.Counterparty AS SalesDocumentCounterparty, 
    CompanySalesTurnovers.QuantityTurnover AS QuantityTurnover, 
    CompanySalesTurnovers.SalesAmountTurnover AS SalesAmountTurnover 
FROM 
    AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers 
TOTALS SUM(QuantityTurnover), SUM(SalesAmountTurnover) 
BY
    OVERALL, 
    Items, 
    SalesDocumentCounterparty

When the query result is output to a spreadsheet document, the application executes additional queries to get presentations of the Items and Counterparty fields, which reduces the report performance. To avoid getting presentations during the report display operation, add presentation fields to the query and pass them to the report.

Example:

SELECT 
    CompanySalesTurnovers.Items AS Items, 
    CompanySalesTurnovers.Items.Presentation AS ItemPresentation, 
    CompanySalesTurnovers.SalesDocument.Counterparty AS SalesDocumentCounterparty,
    CompanySalesTurnovers.SalesDocument.Counterparty.Presentation AS CounterpartyPresenation, 
    CompanySalesTurnovers.QuantityTurnover AS QuantityTurnover, 
    CompanySalesTurnovers.SalesAmountTurnover AS SalesAmountTurnover
FROM 
    AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers
TOTALS SUM(QuantityTurnover), SUM(SalesAmountTurnover)
BY
    OVERALL, 
    Items, 
    SalesDocumentCounterparty

The ItemPresentation and CounterpartyPresentation fields displayed in the spreadsheet document should be taken from this query.

Note that the output form wizard available in Designer follows the above rules. It automatically adds the required presentation fields into a query and ensures they are displayed in the report.

It is also worth mentioning that getting attributes from reference fields using . (dot) while a report is being displayed can significantly impact the performance. So in order to display the attributes of reference fields, you should get them directly in the query instead of via a reference when the report is displayed.

For example, to display the Code field of an item, use the following query:

SELECT 
    CompanySalesTurnovers.Items AS Items, 
    CompanySalesTurnovers.Items.Presentation AS ItemPresentation,   
    CompanySalesTurnovers.Items.Code AS ItemCode,  
    CompanySalesTurnovers.SalesDocument.Counterparty AS SalesDocumentCounterparty,
    CompanySalesTurnovers.SalesDocument.Counterparty.Presentation AS CounterpartyPresenation, 
    CompanySalesTurnovers.QuantityTurnover AS QuantityTurnover, 
    CompanySalesTurnovers.SalesAmountTurnover AS SalesAmountTurnover
FROM 
    AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers
TOTALS SUM(QuantityTurnover), SUM(SalesAmountTurnover)
BY 
    OVERALL, 
    Items, 
    SalesDocumentCounterparty

And display the ItemCode field in the report instead of getting the Code attribute using a reference.

Complementing query result by days belonging to a specified period

Sometimes one needs a report where totals are calculated for each date in a specified time range with a given periodicity. For example, one might need to get turnovers by weeks irrespective of the actual presence of turnovers in specific weeks. The query language has a PERIODS keyword for this purpose. It is added to the TOTALS clause, after the Period field. The PERIODS keyword is followed by the period type in brackets (one of the following: Second, Minute, Hour, Day, TenDays, Week, Month, Quarter, HalfYear, and Year), and the start and end dates of the required period. If the start and end dates are not specified, the first and last dates of the result are used.

Example:

SELECT 
    ItemAccountingTurnovers.Period AS Period,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers(, , Week, ) AS ItemAccountingTurnovers
ORDER BY 
    Period 
TOTALS SUM(QuantityTurnover) BY
    Period PERIODS(WEEK,,)

The result of this query is supplemented with records for beginning of each week.

Note that since detailed records do not exist for supplemented periods in the query result, the supplemented records are only acquired from the selection if all the periods that participate in the query are retrieved during the iteration through the selection. This is done by specifying the third parameter of the Select() function of the query result.

Example:

PeriodSelection = Result.Select(QueryResultIteration.ByGroups, "Period", "All");
While PeriodSelection.Next() Do
    PeriodArea.Parameters.Fill(PeriodSelection);
    SpreadsheetDoc.Put(PeriodArea, PeriodSelection.Level());
EndDo;

Using filters in queries with virtual tables

In order to simplify coding typical queries, the platform provides a set of virtual tables that can be treated as parametrized queries. The query language provides two filter definition methods: in the WHERE query statement and using a virtual table parameter. This section covers the differences between the two methods.

Let us consider two queries to the same accumulation register that select the current balance of a specified product. Suppose the register has the following structure:

• Dimension: Item
• Resource: Quantity

1. A query with a filter specified using a query language statement looks like this:

SELECT 
    QuantityBalance
FROM
    AccumulationRegister.Inventory.Balance()
WHERE
    Item = &Item

2. A query with a filter specified using a virtual table parameter looks like this:

SELECT
    QuantityBalance
FROM
    AccumulationRegister.Inventory.Balance(, Item = &Item)

Both queries produce the same result. But when the first query is executed, balances are calculated for all the products and only then the required balance is selected. The second query transfers the filter condition directly to the virtual table and when the query to the virtual table is executed, only the balance for a single product is acquired. So if a restriction is defined by means of query language, first a complete selection from a virtual table is retrieved and only then a filter is applied. And if a query condition is passed to a virtual table, the resulting selection is limited by that condition. Therefore, we recommend that you pass as many filter conditions as possible in virtual table parameters.

For some virtual tables, the filter method impacts not only performance but query execution logics as well. Let us consider the SliceLast virtual table of an information register on the example of the CurrencyExchangeRates information register, which has the following structure:

• Dimension: Currency
• Attribute: Source
• Resource: ExchangeRate

where the Source attribute can have the following values: "Internet" and "Printed media".

The goal is to get the latest currency exchange rates with the values gathered from the printed media.

1. A query with a filter created using a query language statement looks like this:

SELECT
    Currency, ExchangeRate
FROM
    InformationRegister.CurrencyExchangeRates.SliceLast()
WHERE
    Item = &Item 

2. A query with a filter specified using a virtual table parameter looks like this:

SELECT
    Currency, ExchangeRate
FROM
    InformationRegister.CurrencyExchangeRates.SliceLast(, Source = &Source)

Although these queries only differ by the method used to define the filter, their results are different (unlike the results of queries to the accumulation register). The result of the first query does not include the currencies that do not have their latest exchange rates set by the printed media.

In the second query the latest exchange rates are selected for all the currencies that have had their exchange rates set by the printed media at least once. So if a register stores the following data:

Period	Currency	Exchange rate	Source
03/19/2014	USD	31.00	printed media
03/19/2014	EUR	34.00	printed media
03/20/2014	USD	31.10	Internet

The first query returns:

Currency	Exchange rate
EUR	34.00

The second query returns:

Currency	Exchange rate
USD	31.00
EUR	34.00

Using FOR UPDATE clause in the query language

The FOR UPDATE clause is intended for locking specific data (which is avaialble for reading from a transaction belonging to another connection) in advance while it is being read, to avoid deadlocks later, when it will be written. You can specify the tables whose data will be modified after reading. In this scenario another connection will be waiting for the data to be released since the moment when the data reading is started within a transaction, i.e. it will not be able to read locked data until the locking transaction is completed.
Not that the lock that prohibits modification of data being read in a transaction is set independently of the FOR UPDATE clause. This means that if some data is being read within a transaction, this data cannot be modified before the lock is released. If a query is executed outside of a transaction, it is capable of reading locked data.

Locks are set during the query execution and released when the transaction is completed. If a query is executed outside of a transaction, the FOR UPDATE clause is ignored.

If the FOR UPDATE clause is not followed by any table names, data read from all the tables involved in the query is locked. If tables are specified, only the data from these tables is locked. For locking purposes you can specify only top-level tables (i.e. not the tabular sections) that are involved in the query. Specify the table names instead of their aliases defined in the query. If you specify a virtual table, the data of all the tables that form the virtual table is locked. Specify the virtual table name without any parameters.

In this example of using the FOR UPDATE clause a query locks the balance register:

SELECT 
   Doc.Date, 
   Doc.Ref, 
   Doc.Company, 
   Doc.Counterparty, 
   Doc.MutualSettlementAgreement,
   Doc.DocumentTotal, 
   Doc.MutualSettlementAgreement.MonitorTotalDebt 
       AS MonitorTotalDebt,
   Doc.MutualSettlementAgreement.MaximumTotalDebt
       AS MaximumTotalDebt,
   MutualSettlementsByAgreement.AmountBalance AS AmountBalanceByAgreement
FROM 
   Document.RetailSale Doc

LEFT JOIN // Monitoring the debt amount for the agreement
   AccumulationRegister.CounterpartiesMutualSettlementsCompanies.Balance(, 
             MutualSettlementAgreement = &MutualSettlementAgreement) 
      AS MutualSettlementsByAgreement
   ON True

WHERE 
   Doc.Ref = &DocumentRef
FOR UPDATE AccumulationRegister.MutualSettlementsByAgreement.Balance 
                           // While the table of register balances is being read,
                           // no data can be written to this table
                           // to avoid collisions

Using custom expressions in query language totals

The query language allows you to use custom expressions for calculating totals by groupings. This article covers some specifics of using custom expressions in total fields and provides usage examples.

Custom expressions in totals

The syntax and functionality of an expression for calculating totals are both similar to a regular query language expression though there are some differences:

• in total calculation expressions you cannot use the IN and IN HIERARCHY operations with nested queries;
• in total calculation expressions the result of Presentation() function can be used in another expression.

If a custom expression is used to calculate totals, you have to specify the name of the field that will store the calculation result. To do this, add a field alias from the query selection list after the description of the total expression (after the optional AS keyword).

Example:

TOTALS
    100 * Sum(Profit) / SUM(Turnover) AS ProfitPercentage

In this example the expression result is stored to the ProfitPercentage field. Note that the ProfitPercentage field must be present in the query selection list.

All the fields used in the total calculation expressions must be present in the query selection list. For the field names, you can use the aliases of the selection list fields.

Only those fields by which totals are calculated can be used in the expressions for calculating totals outside of aggregate functions.

Example:

TOTALS
    Items AS Items, // This is correct because the field is present in the list of total groupings
    AmountTurnover AS AmountTurnover, // This is incorrect because the field is absent from the list of total groupings
    SUM(QuantityTurnover) AS QuantityTurnover // This is correct because the field is used in an aggregate function
BY
    Items,
    Counterparty

If a grouping field is used in a total expression and this field has not been used to get a grouping, the field value is NULL.

Usage examples

Calculating expressions based on aggregate functions

To calculate expressions based on aggregate functions, it is sufficient to provide an expression that uses aggregate functions.

Example:

SELECT
    Items,
    AmountTurnover AS Turnover,
    AmountProfit AS Profit,
    100 * AmountProfit / AmountTurnover AS ProfitPercentage
FROM
    AccumulationRegister.ProfitAccounting.Turnovers
TOTALS
    SUM(Turnover),
    SUM(Profit),
    100 * SUM(Profit) / SUM(Turnover) AS ProfitPercentage
BY
    Items HIERARCHY

In this example the ProfitPercentage field of the total records will store the value of the sum by the Profit field multiplied by 100 and divided by the value of the sum by the Turnover field.

Calculating totals only for retrieved groupings

To calculate values of some total fields only for groupings whose totals are already retrieved, you can use the fact that the grouping field values are NULL for groupings whose totals are not yet retrieved.

Example:

SELECT
    PrimaryBalance.Account AS Account,
    PrimaryBalance.Currency AS Currency,
    PrimaryBalance.AmountBalance AS AmountBalance,
    PrimaryBalance.CurrencyAmountBalance AS CurrencyAmountBalance
FROM
    AccountingRegister.Primary.Balance AS PrimaryBalance
TOTALS
    SUM(AmountBalance),
    CASE
        WHEN Currency IS NOT NULL
            THEN SUM(CurrencyAmountBalance)
    END AS CurrencyAmountBalance
BY
    Account,
    Currency

In this example the total values for the CurrencyAmountBalance field are only calculated for totals that have the Currency grouping retrieved. So, totals for the Account grouping by CurrencyAmountBalance field are only calculated when the grouping totals are retrieved only inside the Currency grouping.
Another way to calculate totals is checking the number of different values in a grouping and calculating the totals only for the total records that are calculated for a single grouping value.

Example:

SELECT
    PrimaryBalance.Account AS Account,
    PrimaryBalance.Currency AS Currency,
    PrimaryBalance.AmountBalance AS AmountBalance,
    PrimaryBalance.CurrencySumBalance AS CurrencySumBalance
FROM
    AccountingRegister.Primary.Balance AS PrimaryBalance
TOTALS
    SUM(AmountBalance),
    CASE
        WHEN COUNT(DISTINCT Currency) = 1
            THEN SUM(CurrencyAmountBalance)
    END AS CurrencyAmountBalance
BY
    Account,
    Currency

In this example, calculation of totals for the CurrencyAmountBalance field is performed for those total records that include a single value of the Currency field.

Redefining grouping point presentations

If a field is a presentation of a grouping field, redefining the expression for this field leads to displaying the total expression result as a field presentation. This feature allows you to redefine presentations for groupings.

Example:

SELECT
    SalesTurnovers.Items AS Items,
    SalesTurnovers.Items.Code AS ItemCode,
    PRESENTATION(SalesTurnovers.Items) AS ItemPresentation,
    SalesTurnovers.QuantityTurnover AS QuantityTurnover,
    SalesTurnovers.AmountTurnover AS AmountTurnover
FROM
    AccumulationRegister.Sales.Turnovers AS SalesTurnovers
TOTALS
    ItemCode + " " + ItemPresentation AS ItemPresentation,
    SUM(QuantityTurnover),
    SUM(AmountTurnover)
BY
    Items

In this example the presentation of the Items field in the total records is an expression containing a code and the default presentation.

Sorting in nested queries

The query language supports sorting in nested queries. You can use sorting in a nested query if the query has a limitation on the number of retrieved records. This means that a nested query may only include sorting if it includes the TOP statement.

For example, let us consider a query that gets sales volumes for the five most expensive products.

SELECT
    NestedQuery.Ref,
    SalesTurnovers.QuantityTurnover,
    SalesTurnovers.AmountTurnover
FROM
    (SELECT TOP 5
        Items.Ref AS Ref
    FROM
        Catalog.Items AS Items

    ORDER BY
        Items.PurchasePrice DESC) AS NestedQuery
        LEFT JOIN AccumulationRegister.Sales.Turnovers(,,,
            ITEM IN
                (SELECT TOP 5
                    Items.Ref AS Ref
                FROM
                    Catalog.Items AS Items
                ORDER BY
                    Items.PurchasePrice DESC)) AS SalesTurnovers
        BY NestedQuery.Ref = SalesTurnovers.Items

In this query, the nested query gets the five products with the highest value in the PurchasePrice field, and in the main query the retrieved products are linked to the sales register that provides the sales data. To limit the calculation of turnovers, the list of the most expensive products is also passed to the condition of the AccumulationRegister.Sales.Turnovers table.

Using the ISNULL() function

When you work with query language, you sometimes need to replace the NULL value with some other value. You might need this to get warehouse balances for all the products. This article covers the usage of the ISNULL() query language function in such scenarios and also considers other possible solutions.

The ISNULL function

The query language includes the ISNULL() function, which is intended to replace an expression with another one if its value is NULL. It uses the following syntax:

ISNULL(<Expression being checked>, <Substitution expression>)

This function returns the value of the first parameter if it is not NULL, otherwise it returns the value of the second parameter.

Example:

SELECT
    CatalogItems.Description,
    ISNULL(ItemAccountingBalance.AmountBalance, 0) AS QuantityBalance
FROM
    Catalog.Items AS CatalogItems
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccountingBalance.Items = CatalogItems.Ref
WHERE
    CatalogItems.IsFolder = FALSE

This code gets all the items of the products catalog, and then gets the current balances for each product in the accumulation register. Since the virtual table does not return records for products that have no balances, as a result of the join the ItemAccountingBalance.QuantityBalance field will store the NULL values for the products without balances. In order to have 0 instead of NULL in the query result, we are using the function ISNULL() that performs the required substitution.

Using the CASE operation

Another method of dealing with the described situation is using the CASE query language operation. A query similar to the one above will look like this:

SELECT
    CatalogItems.Description,
    CASE WHEN ItemAccountingBalance.QuantityBalance IS NULL THEN 0 ELSE ItemAccountingBalance.QuantityBalance AS QuantityBalance
FROM
    Catalog.Items AS CatalogItems
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccountingBalance.Items = CatalogItems.Ref
WHERE
    CatalogItems.IsFolder = FALSE

The result of this query is identical to that of the query in the previous section.

Using the ISNULL() function is preferable to using the CASE operation for the following reasons: the code with ISNULL() is shorter, which means better readability. In addition, when the expression being checked is a complex function (aggregate function, among others), the ISNULL() expression can be calculated faster than the CASE expression.

Specifics of the ISNULL function

While ISNULL() is an equivalent of the CASE operation that checks whether a value is NULL, there is still a difference: when the expression is a string or a number, the substitution expression is converted to that type.

For example, when the expression being checked has String(5) type and the substitution expression has String(10) type, the result is converted to String(5) type. So when the function returns the substitution expression, its value is cut to five characters.

It is similar for numeric expressions: the value is converted to the type of the expression being checked, i.e. the substitute value might be cut. If a value cannot be converted, the query language aborts query execution with an error. For example, converting the number 1000 to Number(2) type results in an error.

Details on how to use the group inclusion operator

1C:Enterprise query language includes a generalized form of the IN/ NOT IN operator. This section describes how this operator is executed. This knowledge helps with query optimization.

Group inclusion operator

The inclusion operator has the following syntax:

<Expression> IN (<List of values>)

The inclusion operator is one of the comparison operators defined in 1C:Enterprise query language. Its value is True if the value of the left operand is in the list of values of the right operand. The list of values can be defined as:

• a list of expressions;
• a parameter with a value that is a value collection;
• a nested query.

If a list of values is defined by a nested query, you can use the generalized form of the inclusion operator, i.e. the group inclusion operator:

(<Expression 1>, ..., <Expression N>) IN (<Nested query>)

A nested query should define a selection of N columns.

The group inclusion operator value is True if the record (<Expression 1>, ..., <Expression N>) matches one of the records in the selection defined by the nested query. For example, suppose the left operand is a list:

(1, 2, 3).

If the result of the nested query is the following selection:

1, 1, 1
2, 2, 2
2, 2, 3

the result of this group inclusion operator is False. If the result of the nested query is another selection:

1, 1, 1
1, 2, 3
2, 2, 3

the result of the group inclusion operator is True.

In addition to the group inclusion operator (IN) the query language also provides a noninclusion operator (NOT IN). The result of this operator is a negation of the IN operator with the same operands. The explanation above regarding the IN operator is valid for NOT IN as well.

Implementation of the group inclusion operator

Unlike a simple inclusion operator that has some analogues in most database management systems (the SQL language IN operator), the group inclusion operator does not have an exact equivalent in SQL. Therefore, when you use the group inclusion operator, you should take into account the way it is translated into SQL. The following operator:

(<Expression 1>, ..., <Expression N>) IN (
    SELECT <Column 1>, ..., <Column N>
    FROM <Sources>
    WHERE <Condition>
)

is executed as follows in the DBMS:

EXISTS(
    SELECT 1
    FROM <Sources>
    WHERE (<Condition>) AND <Expression 1> = <Column 1> AND ... AND <Expression N> = <Column N>
)

If a nested query contains aggregate functions and/or a GROUP BY section, the following group inclusion operator:

(<Expression 1>, ..., <Expression N>) IN (
    SELECT <Column 1>, ..., <Column N>
    FROM <Sources>
    WHERE <Condition 1>
    GROUP BY <Grouping list>
    HAVING <Condition 2>
)

is coded as follows in SQL:

EXISTS(
    SELECT 1
    FROM <Sources>
    WHERE <Condition 1>
    GROUP BY <Grouping list>
    HAVING (<Condition 2>) AND <Expression 1> = <Column 1> AND ... AND <Expression N> = <Column N>
)

Notes on execution speed

The execution speed of a query containing the group inclusion operator depends on the capability of the query plan optimizer of the database management system to efficiently implement the EXISTS operator with a nested query. Therefore, it is difficult to predict if a specific query will be executed efficiently in general. However, it is possible to offer some recommendations that may help generate efficient queries.

Short tables

Use of a group inclusion operator in a query does not decrease performance if the nested query generates a selection from tables with small numbers of records, and these numbers cannot significantly increase during the operation of the system with the growth of the accumulated database.

Indexes

You should remember that a query nested in an EXISTS operator is executed multiple times when checking the filter condition for each record. Therefore, if tables used in a nested query may include a large number of records, any search within the tables should be optimized by defining the appropriate indexes:

• for the values of columns <Column 1>, ..., <Column N> only use names of table fields (not expressions);
• select a field from those used as values of columns <Column 1>, ..., <Column N> with its values repeated least frequently (the most selective field) and enable indexing by this field;
• do not use aggregate functions and groupings in a query nested in a group inclusion operator;
• include the most selective fields in the list of group inclusion operator. For example, when you check warehouse balances by product items from the tabular section of the PurchaseOrder document, which is referenced by the DocumentRef parameter, the following query will be executed slowly because it is impossible to generate an index from a group of fields (Items, ItemProperty, Quality) in the Document.PurchaseOrder.Goods table.

SELECT
    Doc.Items.Presentation AS ItemsPresentation,
    Doc.Items.UnitOfMeasurement.Presentation AS UnitOfMeasurementPresentation,
    Doc.ItemProperty AS ItemProperty,
    Doc.ItemSeries AS ItemSeries,
    Doc.Quality AS Quality,
    SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3))) AS DocumentQuantity,
    ISNULL(MAX(Balance.QuantityBalance), 0) AS BalanceQuantity
FROM 
    Document.SalesInvoice.Goods AS Doc
    LEFT JOIN
        AccumulationRegister.GoodsToBeShippedFromWarehouses.Balance(,
            Warehouse = &Warehouse AND (Items, ItemProperty, Quality) IN (
                SELECT
                    Doc.Items, Doc.ItemProperty, Doc.Quality
                FROM
                    Document.SalesInvoice.Goods AS Doc
                WHERE
                    Doc.Ref = &DocumentRef
                    AND NOT Doc.Items.Package 
            ) AND &ShippingDocument = ShippingDocument) AS Balance
    ON 
        Doc.Items = Balance.Items
        AND Doc.ItemProperty = Balance.ItemProperty
        AND Doc.Quality = Balance.Quality
        AND ((NOT Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = Doc.ItemSeries) OR
            (Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = &EmptySeries))
WHERE
    Doc.Ref = &DocumentRef
GROUP BY
    Doc.Items,
    Doc.ItemProperty,
    Doc.ItemSeries,
    Doc.Quality,
    &ShippingDocument
HAVING
    ISNULL(MAX(Balance.QuantityBalance), 0) < SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3)))

The following query is different from the previous one in that the group inclusion operator has the Warehouse field added, which is used for indexing in the Document.PurchaseOrder.Goods table. This results in significant reduction of query execution time.

SELECT
    Doc.Items.Presentation AS ItemsPresentation,
    Doc.Items.UnitOfMeasurement.Presentation AS UnitOfMeasurementPresentation,
    Doc.ItemProperty AS ItemProperty,
    Doc.ItemSeries AS ItemSeries,
    Doc.Quality AS Quality,
    SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3))) AS DocumentQuantity,
    ISNULL(MAX(Balance.QuantityBalance), 0) AS BalanceQuantity
FROM 
    Document.SalesInvoice.Goods AS Doc
    LEFT JOIN
        AccumulationRegister.GoodsToBeShippedFromWarehouses.Balance(,
            (Warehouse, Items, Quality, ItemProperty) IN (
                SELECT
                    Doc.Ref.Warehouse, Doc.Items, Doc.Quality, Doc.ItemProperty
                FROM
                    Document.SalesInvoice.Goods AS Doc
                WHERE
                    Doc.Ref = &DocumentRef
                    AND NOT Doc.Items.Package  
            ) AND &ShippingDocument = ShippingDocument) AS Balance
     ON 
        Doc.Ref.Warehouse = Balance.Warehouse
        AND Doc.Items = Balance.Items
        AND Doc.Quality = Balance.Quality
        AND Doc.ItemProperty = Balance.ItemProperty
        AND ((NOT Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = Doc.ItemSeries) OR
            (Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = &EmptySeries))

WHERE
    Doc.Ref = &DocumentRef
GROUP BY
    Doc.Items,
    Doc.ItemProperty,
    Doc.ItemSeries,
    Doc.Quality,
    &ShippingDocument
HAVING
    ISNULL(MAX(Balance.QuantityBalance), 0) < SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3)))

Temporary tables

If a query nested in the group inclusion operator is complex, contains aggregate functions, and uses tables with large numbers of records, execution of the group inclusion operator might be extremely inefficient. In this case we recommend the following:

• execute the nested query separately and store its result to a temporary table;
• define an index in this temporary table either using all the fields or the most selective subset of fields;
• use the resulting temporary table in the query nested in the group inclusion operator.

Specifics of using the GROUP BY clause

1C:Enterprise query language offers the GROUP BY clause for grouping (collapsing) records by specific fields. This clause is similar to the GROUP BY clause of the SQL standard but does have a few peculiarities.

The major peculiarity is that when the GROUP BY clause contains a field, all the fields received from it using . (dot) are also considered grouped. Example:

SELECT 
 CustomerOrder.Counterparty, 
 CustomerOrder.Counterparty.Code, 
 CustomerOrder.Counterparty.Description, 
 SUM(CustomerOrder.DocumentTotal) AS DocumentTotal 
FROM 
 Document.CustomerOrder AS CustomerOrder
GROUP BY 
 CustomerOrder.Counterparty

In this example automatic grouping by the CustomerOrder.Counterparty.Code and CustomerOrder.Counterparty.Description fields will be performed because the GROUP BY clause contains the CustomerOrder.Counterparty field with its data received using . (dot).

Specifics of working with virtual table of calculation register schedule data

A virtual table CalculationRegister.<RegisterName>.ScheduleData is defined for calculation registers that support an action period. A calculation register of this type should be linked to a nonperiodic information register that will provide schedule data.

To simplify getting schedule data, the system has a virtual table defined that includes the following virtual fields in addition to other fields:

• <Name of schedule resource>BasePeriod
• <Name of schedule resource>ActionPeriod
• <Name of schedule resource>RegistrationPeriod
• <Name of schedule resource>ActualActionPeriod

This means that for each numeric resource of the information register assigned as a schedule for a calculation register, you can get its sum for register rows, taking into account the base period of the calculation register row, action period, registration period, and actual action period.

Upon generation of a virtual table of schedule data, the tables of the calculation register and information register are joined and when the field <Name of schedule resource>ActualActionPeriod is retrieved, the third joined table is the table of the actual action period of the calculation register. Since the data for all the four listed fields is received by joining with the information register table based on various conditions, it means that up to four joins with the information register table are executed.

Upon getting virtual tables, the system attempts to act in a sensible way, i.e., it only performs as many joins as are needed to get the virtual table fields listed in the "SELECT" section.

This ensures that when you write query code, you do not need to select virtual table fields "just in case". This recommendation may sound too general, but remember that getting an unnecessary field in a query to actual tables or other virtual tables will generally increase network data traffic and result in a slight decrease in performance. It is obvious that getting five register fields is hardly any different from getting another, sixth field unless we are referring to large fields. But when you select from a virtual table of the schedule data, you should note that adding one more virtual field from the list changes the situation dramatically. Query execution times may vary cardinally. So the recommendation of coding queries in an optimum fashion with a careful selection of the number of selected fields is particularly valid for this virtual table.

Also note that the performance of a virtual table query is impacted significantly by the number of virtual field types rather than the number of fields.

Let us consider an example. The information register assigned as a schedule has the resources NumberOfDays and NumberOfHours. Let us review the following three queries.

1.

SELECT 
    NumberOfDaysRegistrationPeriod
FROM 
    CalculationRegister.Payroll.ScheduleData(Company = &Company)

2.

SELECT 
    NumberOfDaysRegistrationPeriod, 
    NumberOfHoursRegistrationPeriod
FROM 
    CalculationRegister.Payroll.ScheduleData(Company = &Company)

3.

SELECT 
    NumberOfDaysRegistrationPeriod, 
    NumberOfHoursActionPeriod
FROM 
    CalculationRegister.Payroll.ScheduleData(Company = &Company)

When you compare execution times, you will notice that the second query execution time is almost the same as that of the first query, despite the fact that a new virtual field is added. But execution of the third query takes notably more time (possible a few times more) than the second one, even though the number of fields selected is the same.

This is due to the fact that in order to get the fields NumberOfDaysRegistrationPeriod and NumberOfHoursRegistrationPeriod, a single join to the information register by the value in the RegistrationPeriod field (irrespective of the number of register resources to be summed) is executed. And when you also get the field NumberOfHoursActionPeriod, yet another join to the information register is established by the value of the ActionPeriod.

Specifics of working with the Presentation field and Presentation() function of the query language

To display reference fields in a report, you need to get reference field presentation in a query and use this presentation instead of the reference for displaying the field. This section describes some specifics of the Presentation field and the Presentation() function that get a presentation. For details on displaying reference fields, see Display of reference fields.

Presentation field

Each object table in the infobase has a virtual Presentation field. This field contains text object presentation. You can retrieve this field in a query just like other table fields, but you cannot execute any operations with this field. This is due to the fact that this is a virtual field, and when you get this field from the database, the query actually gets multiple fields and converts the received values into a string after the query result is generated. Therefore, the only thing you can do with the Presentation field is get it as a part of a query result.

Therefore, we recommend that you do not sort the query result by the Presentation field because it will have an unexpected effect: the query result will be sorted in the order of ascending object references. For details see Specifics of sorting by reference fields.

Presentation() function

The presentation function is intended to get a text presentation of any value that can be retrieved using the query language. The Presentation() function can be used both for reference and primitive types. For reference types, the function result is identical to getting the Presentation field from a reference passed as a function parameter. For primitive types, the function returns a string that is a result of conversion of the value that has been passed as a parameter. A distinguishing feature of this function is that its result cannot be used in any expression. This feature is due to the fact that converting values into a string is performed when data is received from the query result, since conversion of an arbitrary value to a string upon query execution on the server side is not performed because local settings should be taken into account when converting values to a string.

Using the Presentation() function has a number of advantages compared to using the Presentation field. For example, if the field from which a presentation is received can contain both reference and primitive types, getting the Presentation field from this field using . (dot) does not return presentations for the primitive values. But if you apply the Presentation() function to this field, a string presentation is generated irrespective of the value type in the field. In addition, if the Presentation() function is applied to a field that is a reference to more than three tables, the query language only gets reference values from the database while the presentation values are retrieved via one or more additional queries. This behavior makes retrieving presentations for the fields that reference a large number of tables (for example, fields that reference any catalog) more efficient due to the fact that the executed query does not include a large number of connections required to get the presentation fields.

The Presentation() function can also be handy when you get the presentation of an enumeration field and the query is executed via the COM connection.

Specifics of connecting to the virtual balances table

Introduction

Developers of configurations that automate retail and merchandising activities frequently need algorithms that check the availability of a required product quantity in a warehouse before processing the papers. This is handled efficiently with the query language by connecting a document tabular section with a balances table in order to check balances. This section covers a few issues that developers might encounter during the implementation of this feature.

Checking balances

A query to get the balances of products available in a tabular section has the following format:

SELECT
    SalesOrderContent.Items,
    ItemAccountingBalance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccountingBalance.Items = SalesOrderContent.Items
WHERE
    SalesOrderContent.Ref = &Document

Be sure to pay attention to the following factors. First of all, when a product is not available in the warehouse, the ItemAccountingBalance.QuantityBalance field has the NULL value, so any comparison with it is invalid and returns a result that is treated as the FALSE value. Take this into account when creating a query. For example, to get products whose warehouse quantities might be lower than the quantities in a document, use the following query format:

SELECT
    SalesOrderContent.Items,
    ItemAccounting.Balance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccounting.Balance.Items = SalesOrderContent.Items
WHERE
    SalesOrderContent.Ref = &Document AND
    (ItemAccounting.Balance.QuantityBalance < SalesOrderContent.Quantity OR
        ItemAccounting.Balance.QuantityBalance IS NULL)

This query checks that the warehouse quantity of a product is less than the required quantity or the product is not available at all.

The next important factor is limiting the number of balance calculations. In this example the virtual table of balances first calculates balances for all the products and then connects the products found in the tabular section to the tabular section. To ensure calculation of balances of only those products that are on the sales order, you can pass the limitation to the balances table (see Using filters in queries with virtual tables). The corrected query is as follows:

SELECT
    SalesOrderContent.Items,
    ItemAccountingBalance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance(,
            Item IN (
                SELECT Items
                FROM Document.SalesOrder.Content
                WHERE Ref = &Document)) AS ItemAccountingBalance
        ON ItemAcountingBalance.Items = SalesOrderContent.Items
WHERE
    ItemAccountingBalance.Ref = &Document AND
    (ItemAccountingBalance.QuantityBalance < SalesOrderContent.Quantity OR
        ItemAccountingBalance.QuantityBalance IS NULL)

Another important thing is that a document might have multiple rows with the same product. To check balances correctly, the query should be modified so that the total quantity of a product in the document is compared to the balances. The modified query is as follows:

SELECT
    Content.Items,
    Content.Quantity,
    ItemAccountingBalances.QuantityBalance
FROM
    (SELECT 
         SalesOrderContent.Items AS Items,
         SUM(SalesOrderContent.Quantity) AS Quantity
     FROM
         Document.SalesOrder.Content AS SalesOrderContent
     WHERE SalesOrderContent.Ref = &Document
     GROUP BY SalesOrderContent.Items) AS Content
         LEFT JOIN AccumulationRegister.ItemAccounting.Balance(,
             Items IN (
                 SELECT 
                     Document.SalesOrder.Content.Items 
                 FROM
                     Document.SalesOrder.Content 
                 WHERE
                     Document.SalesOrder.Content.Ref = &Document)
             ) AS ItemAccountingBalances 
         ON ItemAccountingBalances.Items = Content.Items
WHERE
    (ItemAccountingBalances.QuantityBalance < Content.Quantity 
    OR (ItemAccountingBalances.QuantityBalance) IS NULL )

As you can see from the query, we started by calculating the total number of each product in the document and only then executed all the operations that involve comparison of balances.

The last thing to be aware of is displaying information on insufficient product quantity to a user. As we have described in detail in Display of reference fields, in order to display product information to a user, we recommend that you get product presentation using a query and display this presentation to the user. Here is a modified query that additionally gets product presentations:

SELECT
    Content.Items, 
    Content.Items.Presentation AS Presentation, 
    Content.Quantity,
    ItemAccountingBalances.QuantityBalance
FROM
    (SELECT 
         SalesOrderContent.Items AS Items,
         SUM(SalesOrderContent.Quantity) AS Quantity
     FROM
         Document.SalesOrder.Content AS SalesOrderContent
         WHERE SalesOrderContent.Ref = &Document
         GROUP BY SalesOrderContent.Items) AS Content
             LEFT JOIN AccumulationRegister.ItemAccounting.Balance(,
                 Items IN (
                     SELECT 
                         Document.SalesOrder.Content.Items 
                     FROM
                         Document.SalesOrder.Content 
                     WHERE
                         Document.SalesOrder.Content.Ref = &Document)
                 ) AS ItemAccountingBalances
             ON ItemAccountingBalances.Items = Content.Items
WHERE
    (ItemAccountingBalances.QuantityBalance < Content.Quantity 
     OR (ItemAccountingBalances.QuantityBalance) IS NULL )

Then you need to display the Presentation field to the user.

Example:

Result = Query.Execute();
If Not Result.IsEmpty() Then
    Message("Insufficient goods:");
    Selection = Result.Choose();
    While Selection.Next() Do
        Message(Selection.Presentation + ": required: " + Row(Selection.Quantity)
                  + ", available: " + Row(Selection.QuantityBalance));
    EndDo;
EndIf;

Conclusion

The section describes some specifics of checking product balances. We have provided example queries for getting data required for checking balances. You can use these examples when you implement actual queries.

Specifics of sorting by reference fields

When you work with queries, it is sometimes necessary to sort query results by presentation of some reference field. For example, you might need to sort a product list by presentation. Note that sorting directly by the Presentation field leads to a somewhat unexpected result: data is sorted according to the ascending order of object references. The reason of this effect is the fact that the Presentation field is a virtual one and at the database level it consists of multiple fields. The presentation value is formed based on these fields at the moment it is retrieved. If sorting by this field is performed, sorting by the actual fields that form it is applied consequently, and the result is sorted by internal field reference instead of by string presentation.

Example of incorrect sorting:

SELECT
    Items.Code,
    Items.Description,
    Items.PurchasePrice
FROM
    Catalog.Items AS Items
WHERE
    Items.IsFolder = FALSE
ORDER BY 
    Items.Presentation

In reality, sorting by presentation is questionable in terms of the usefulness of the result. For example, let us take a look at document presentations: these strings contain dates, and sorting dates as strings does not provide any meaningful result. To ensure that sorting meets user expectations, use sorting directly by reference field and add the AUTOORDER keyword to the query. If this is the case, a query retrieves actual fields for each reference field required to sort it (date and number for documents, default presentation for catalogs) and applies sorting by these actual fields.

Example of correct sorting:

SELECT
    Items.Code,
    Items.Description,
    Items.PurchasePrice
FROM
    Catalog.Items AS Items
WHERE 
    Items.IsFolder = FALSE
ORDER BY
    Items.Ref
AUTOORDER

Specifics of DATEDIFF function of the query language

The DATEDIFF function of the query language is intended to get the difference between two dates in a specified measurment unit. The syntax of the function is as follows:

DATEDIFF(<Expression>, <Expression>, Second | Minute | Hour | Day | Month | Quarter | Year)

The first parameter value is subtracted from the second parameter value.

The third parameter defines the measurement unit for the function result. You can use the following values in the third parameter: Second, Minute, Hour, Day, Month, Quarter, and Year. When calculating the result, the function ignores the measurement units that are smaller than the one specified in this parameter.

For example, if you specify Hour in the third parameter, minutes and seconds in the first two parameters are ignored. If you specify Month in the third parameter, then days, hours, minutes, and seconds are ignored.

The following table provides examples of the function return values:

Function	Result
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), SECOND)	82 652
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), MINUTE)	1 378
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), HOUR)	23
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), DAY)	1
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), MONTH)	1
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), QUARTER)	1
DATEDIFF(DATETIME(2002, 12, 31, 10, 20, 34), DATETIME(2003, 01, 01, 9, 18, 06), YEAR)	1
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), SECOND)	31 535 999
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), MINUTE)	525 599
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), HOUR)	8 759
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), DAY)	364
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), MONTH)	11
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), QUARTER)	3
DATEDIFF(DATETIME(2002, 01, 01, 0, 0, 0), DATETIME(2002, 12, 31, 23, 59, 59), YEAR)	0

 

Specifics of executing SUBSTRING() function of the query language

This section covers the specifics of executing the SUBSTRING() function of the query language in the client/server mode and the tips for building queries with this function.

SUBSTRING() function

In the 1C:Enterprise query language the SUBSTRING() function has the following format: SUBSTRING(<Source string>, <Start point>, <Length>). It is applied to string data. It selects a part of the <Source string> that begins with the character at <Start point> position (the first character is considered to have number 1) and has a length of <Length> characters. The result of the SUBSTRING() function is a string of variable length and its length is considered unlimited if <Source string> has unlimited length and the <Length> parameter is not a constant or is greater than 1024.

Calculation of the SUBSTRING() function on an SQL Server

In the client/server mode, the SUBSTRING() function is implemented using the SUBSTRING() function of the respective SQL operator passed to the database server (SQL Server). The server calculates the result type of the SUBSTRING() function using sophisticated rules taking into account the type and values of its parameters as well as the context it is used in.

In most cases, these rules do not impact the execution of the 1C:Enterprise query, but sometimes the maximum length of the resulting string calculated by the SQL Server is important for query execution. Note that in some SUBSTRING() function usage scenarios the maximum length of its result can be equal to the maximum number of the limited-length string, which is 4000 characters in SQL Server. This may result in unexpected termination of query execution.

For example, the following query:

SELECT
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN SUBSTRING(Presentation, 0, 200) 
        ELSE NULL 
    END AS Presentation,
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN SUBSTRING(Presentation, 0, 200) 
        ELSE NULL 
    END AS Presentation1
FROM
    InformationRegister.ContactInfo AS ContactInfo
ORDER BY
    Presentation,
    Presentation1

is terminated with the following message:

DBMS error:
Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
HRESULT=80040E14, SQLSTATE=42000, native=8618

This is due to the fact that the maximum string length is calculated by Microsoft SQL Server using the following expression:

CASE
    WHEN Type = &LegalAddressOfIndividual 
    THEN SUBSTRING(Presentation, 0, 200) 
    ELSE NULL 
END AS Presentation

equals 4000 characters. This is why the length of a record that contains two such fields is more than 8000 bytes allowed to execute the sorting operation.

Due to this behavior of the SUBSTRING() function on the SQL Server, we recommend that you do not use the SUBSTRING() function to convert unlimited length strings to limited length strings. We recommend that you use CAST() instead. So the above example can be rewritten as follows:

SELECT
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN CAST(Presentation AS String(200)) 
        ELSE NULL 
    END AS Presentation,
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN CAST(Presentation AS String(200)) 
        ELSE NULL 
    END AS Presentation1
FROM
    InformationRegister.ContactInfo AS ContactInfo
ORDER BY 
    Presentation,
    Presentation1

Specifics of using "IN HIERARCHY" clause in query language

When you use the IN HIERARCHY query language clause, always remember that if you pass a null reference to the parameter, query execution will be slowed because the system will check each item to find out whether it belongs to the hierarchy root. To prevent this, the IN HIERARCHY condition should only be used with a specified item (group), so it is best choice to make such fields the required ones. If the parameter is not specified (is a null reference), it is better to remove this condition from the query altogether.

Accessing fields from other fields using . (dot )

In the query language you can access a field by specifying it after another field, separated by . (dot). On the one hand, this allows you to create short queries. But on the other hand, you should always understand the background of every such action. This section covers accessing fields using . (dot) and provides tips on how to optimize access to fields.

Accessing a field using . (dot)

Let us consider the following query:

SELECT
    Items.Description, 
    QuantityTurnover
FROM 
    AccumulationRegister.ItemsAccounting.Turnovers AS ItemsAccountingTurnovers

In this query the Description field is accessed by adding it after . (dot) to the Items field. When such a query is executed, 1C:Enterprise query language generates an implicit connection to the Items catalog and gets the Description field from it. Therefore, the query that is actually executed has the following format:

SELECT
    CatalogItems.Description, 
    QuantityTurnover 
FROM
    AccumulationRegister.ItemsAccounting.Turnovers AS ItemsAccountingTurnovers  
    LEFT JOIN Catalog.Items AS CatalogItems 
    ON ItemsAccountingTurnovers.Items = CatalogItems.Ref

Note that if a query gets multiple fields from a single field using . (dot), the platform creates a single connection to the table referenced by these fields. For instance, if in the first example you get the Description and Code fields from the Items field using . (dot), a single connection to the Items table is created instead of two connections.

Accessing a Ref field using . (dot)

Do not get the Ref field from another field using . (dot). When you get the Ref field using this method, an additional connection is created, which will most likely impact the query performance.

For example, in the following query the Ref field is accessed from the Counterparty field using . (dot).

SELECT
    PurchaseOrder.Date,
    PurchaseOrder.Number
FROM
    Document.PurchaseOrder AS PurchaseOrder
WHERE
    PurchaseOrder.Counterparty.Ref = &Counterparty

This results in the creation of a redundant connection to the Counterparties table, which will probably slow down the query execution. This query should be modified as follows:

SELECT
    PurchaseOrder.Date,
    PurchaseOrder.Number
FROM
    Document.PurchaseOrder AS PurchaseOrder
WHERE
    PurchaseOrder.Counterparty = &Counterparty

Using . (dot) to access fields from the fields that reference multiple objects

If a field used to access another field is a reference to multiple tables, the number of connections established during the query execution is equal to the number of referenced tables that contain the required field.

Example: Suppose the Recorder field of the accumulation register references two documents: PurchaseOrder and SalesOrder. In this case the following query:

SELECT
    ItemsAccounting.Recorder.Number,
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting

will result in an implicit connection to both document tables, i.e. two connections will be established. The executed query will look as follows:

SELECT
    CASE
        WHEN ItemsAccounting.Recorder REFS Document.SalesOrder
            THEN SalesOrder.Number
        WHEN ItemsAccounting.Recorder REFS Document.PurchaseOrder
            THEN PurchaseOrder.Number 
    END, 
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting
        LEFT JOIN Document.SalesOrder AS SalesOrder 
        ON ItemsAccounting.Recorder = SalesOrder.Ref
        LEFT JOIN Document.PurchaseOrder AS PurchaseOrder
        ON ItemsAccounting.Recorder = PurchaseOrder.Ref

But if the field used to access another field has AnyRef type, in some cases an implicit connection to all the object tables in the configuration might be established. So, for example, if you use a field of the AnyRef type to get the Presentation field and the configuration has 50 catalogs and 100 documents, 150 connections to different tables will be established.

You should keep this in mind when you design your data structure, as well as when you create queries.

Using CAST to limit the number of connections when accesing fields using . (dot)

When you generate a query, you sometimes know which reference will be present in a specific field that references multiple tables. In this case you can explicitly specify the required table in the query so that the implicit connection is established only with the specified table rather than all the tables referenced by this field.

Example: Suppose the Recorder field of the accumulation register references two documents: PurchaseOrder and SalesOrder, and the WHERE statement narrows the selection to recorder values that reference PurchaseOrder: 

SELECT
    ItemsAccounting.Recorder.Number,
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting 
WHERE
    ItemsAccounting.Recorder REFS Document.PurchaseOrder

Regarding this query, as we know for certain that the result will include only the documents with the recorder referencing the table of purchase orders, you can modify the query to improve its performance:

SELECT
    CAST(ItemsAccounting.Recorder AS Document.PurchaseOrder).Number,
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting
WHERE
    ItemsAccounting.Recorder REFS Document.PurchaseOrder

When this query is executed, only the connection to the Document.PurchaseOrder table is established instead of connecting to both tables referenced by the Recorder field. This improves the query performance.

Sample queries for hierarchical catalog operations

This section describes the solutions of typical tasks related to hierarchical catalogs.

Getting hierarchical catalog items that are subordinate to a specified folder

The IN HIERARCHY clause of the query language is intended for getting subordinate items of a hierarchical catalog. Example:

SELECT
    Items.Code,
    Items.Description AS Description,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items
WHERE
    Items.Ref IN HIERARCHY(&Folder)

This example gets all the items of the Items catalog that belong to the &Folder folder, including the folder itself, its subordinate folders, and items in these subordinate folders.

If you want to get only items and folders subordinate to a specific folder, you can get them by applying a condition to the Parent field. Example:

SELECT
    Items.Code,
    Items.Description AS Description,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items
WHERE
    Items.Parent = &Folder

This query selects items and folders that are subordinate to the folder with &Folder reference.

Checking availability of subordinate items for a catalog item

To check whether a catalog item has subordinate items, you can use the following query:

SELECT TOP 1
    Items.Ref
FROM
    Catalog.Items AS Items
WHERE
    Items.Parent = &Parent

In this example the reference to the item that should be checked for the availability of subordinate items is recorded to the Parent query parameter. After the query execution you have to check whether the result is empty. Subordinate records are available if the result is not empty. Otherwise they are not available. Example:

If Query.Execute().IsEmpty() Then
    Message("No records");
Else 
    Message("Records are available");
EndIf;

Getting all parents of an item

The query language does not offer special means for getting all parents of an item. You can implement this using hierarchical totals, but getting hierarchical totals is optimized to build the totals of a large number of records and is not particularly efficient for getting the parents of a single item. To get all the parent records of an item in a more efficient manner, we recommend going through its parents in small batches in a loop.

Example:

CurrentItemsItem = ItemsItem;

Query = New Query("SELECT 
                      | Items.Parent, 
                      | Items.Parent.Parent, 
                      | Items.Parent.Parent.Parent, 
                      | Items.Parent.Parent.Parent.Parent, 
                      | Items.Parent.Parent.Parent.Parent.Parent 
                      |FROM 
                      | Catalog.Items AS Items 
                      |
                      |WHERE 
                      | Items.Ref = &CurrentItemsItem";

While True Do
    Query.SetParameter("CurrentItemsItem", CurrentItemsItem); 
    Result = Query.Execute(); 
    If Result.IsEmpty() Then 
        Break; 
    EndIf; 
    Selection = Result.Choose(); 
    Selection.Next(); 
    For ColumnNumber = 0 To Result.Columns.Count() - 1 Do 
        CurrentItemsItem = Selection[ColumnNumber]; 
        If CurrentItemsItem = Catalogs.Items.EmptyRef() Then 
            Break; 
        Else 
            Message(CurrentItemsItem); 
        EndIf; 
    EndDo; 

    If CurrentItemsItem = Catalogs.Items.EmptyRef() Then 
        Break; 
    EndIf;
EndDo;

In this example all parents for the reference stored in the ItemsItem variable are displayed in the message window. 5 parents are selected in each loop step.

If the number of levels in a catalog is limited and is not large, you can get all the parents in a single query without using a loop.

Displaying a hierarchical catalog in a report

To display a hierarchical catalog in a report while preserving the hierarchy, use the following query format:

SELECT
    Items.Code,
    Items.Description AS Description,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items 
ORDER BY
    Description HIERARCHY

This query selects all the records from a catalog and sorts them by hierarchy. The result is sorted by name with the hierarchy being taken into account.

For the catalog folders to be above the items, replace the ORDER BY clause with the following in this query:

ORDER BY
    Items.IsFolder HIERARCHY,
    Description

The result will be still sorted by hierarchy, but the folders will be located above the items.

It is also possible to replace the ORDER BY clause with the AUTOORDER clause. Then the result will be sorted according to the catalog settings, i.e. if the catalog settings specify that folders should be above items, they will be above.

You can also get the hierarchical structure of a catalog using totals.

Example:

SELECT
    Items.Code, 
    Items.Description AS Description, 
    Items.PurchasePrice 
FROM Catalog.Items AS Items
WHERE 
    (Items.IsFolder = FALSE)
ORDER BY Description
TOTALS BY Items.Ref ONLY HIERARCHY

Getting totals by hierarchy

In order to get the totals by hierarchy, in the TOTALS BY clause add the HIERARCHY keyword after specifying the field used for calculating totals. Here is an example "Item turnovers" report where totals are retrieved by hierarchy:

SELECT
    ItemAccountingTurnovers.Items AS Items,
    ItemAccountingTurnovers.Items.Presentation,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers AS ItemAccountingTurnovers
TOTALS SUM(QuantityTurnover) BY
    Items HIERARCHY

This query calculates the totals not only for each item but also for the folders that contain the items.

If you do not need totals by items and only need totals by folders, use the ONLY HIERARCHY clause in the totals. Example:

SELECT
    ItemAccountingTurnovers.Items AS Items,
    ItemAccountingTurnovers.Items.Presentation,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers AS ItemAccountingTurnovers
TOTALS SUM(QuantityTurnover) BY
    Items ONLY HIERARCHY

The query returns the totals for the item folders only.

Bitness of results of expressions and aggregate functions in query language

When queries use data operations and functions with a definition that includes specification of length (String, Number) and accuracy (Number), you might need to keep in mind the rules for calculating the length and accuracy of results. This section covers these rules.

String operations

Description of the String data may include the following additional specifications: length (maximum number of characters), allowed length (fixed or variable), unlimited length (False or True). This table lists the rules for calculating additional specifications of the function results based on additional specifications of their string operands.

Function	Unlimited length
	True	False, length = n
		Fixed	Variable
SUBSTRUNG(String, m, k)	Unlimited length	Length k, variable	Length k, variable
MIN(String)	Unlimited length	Length n, fixed	Length n, variable
Max(String)	Unlimited length	Length n, fixed	Length n, variable

The following table covers the rules used to determine additional specifications of the "+" operation (concatenation of strings) that takes two string operands.

Operand 1		Operand 2
		Unlimited length	Length = m
			Fixed	Variable
Unlimited length		Unlimited length	Unlimited length	Unlimited length
Length n	Fixed	Unlimited length	Length n + m, fixed	Length n + m, variable
	Variable	Unlimited length	Length n + m, variable	Length n + m, variable

Operations with numbers

When you perform various conversions of numeric data, keep in mind that a number can contain (integer and fractional parts combined) a maximum of 38 digits. 1C:Enterprise uses decimal numbers with a fixed point. This means that the number 34.28 contains 4 digits, and number 0.000000001 has 9 digits (the 0 before the period is not considered to be a digit), while the number 3200000000 has 10 digits. Bitness of the results of various operations is calculated to exclude possible arithmetical overflows and at the same time to get the most accurate result. During configuration development, selection of bitness of metadata object fields should be based on possible bitness of the data stored in such fields and should not take into account any possible bitness increase due to arithmetical operations and functions.

"+" (addition), "-" (subtraction)

Addition and subtraction operations are always executed precisely in the query language. For this, the length of the fractional part of the result is determined as the maximum length of fractional parts of all operands. The length of the integer part of the result is defined as the maximum length of the integer parts of operands plus 1. If in this case the total number of digits in the integer and fractional parts exceeds 38, the number of digits is reduced to 38 by reducing the number of digits in the fractional part, but to no less than 10.

"*" (multiplication)

Multiplication operations are executed precisely if at least one of the operands is an integer. The length of the fractional part of the result is determined as the maximum length of the operand fractional parts, but no less than 10 digits. The length of the integer part of the result is calculated as a sum of the lengths of the integer parts of the operands. If the total number of digits in the integer and fractional parts exceeds 38, it is reduced to 38.

"/" (division)

The division operation is only executed accurately if both operands are integers and the first operand can be divided by the second one without anything left over. The length of the fractional part of the result is determined as the maximum length of the operand fractional parts, but no less than 10 digits. The length of the integer part of the result equals the sum of the lengths of the integer part of the dividend and fractional part of the divisor. If in this case the total number of digits in the integer and fractional parts exceeds 38, this number is reduced to 38 by reducing the number of digits in the fractional part, but to no less than 10.

SUM

The aggregate function SUM is calculated as precisely as possible. The number of fractional digits of the result equals the number of fractional digits of the operand. The number of digits in the integer part is increased by 7. If in this case the total number of digits in the integer and fractional parts exceeds 38, this number is reduced to 38 by reducing the number of digits in the fractional part, but to no less than 10.

MIN, MAX, AVG

The results of MIN, MAX, and AVG functions have the same number of digits in the integer and fractional parts as their operands.

COUNT, YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, DATEDIFF

The listed functions have a numeric result with 10 digits in the integer part. There is no fractional part.

NOTES: The total number of digits in a number equals the sum of the numbers of digits in its integer and fractional parts. For example, if following the rules above the result has 4 digits in the integer part and 3 digits in the fractional part, its type description is Number(7, 3) In the client/server mode, the number of digits in the fractional part of the division operation ("/") is limited by 6 instead of 10. This is due to the specifics of decimal arithmetic in Microsoft SQL Server. If you find it reasonable to change the calculation rules for length and result accuracy for operations with data, use the CAST operator to specify the required length and accuracy. Note that if you want to change the length and accuracy of an operation result, the CAST operator should be applied to its operand. In this case the length and accuracy of the operation result is calculated using the rules above based on the length and accuracy of the operand specified by CAST. For example, if the field "Attribute1" is of type "Number(8, 0)", for the expression Attribute1 / 3 to have 15 digits in the fractional part, use the following code: CAST(Attribute1 AS Number(23, 15)) / 3

Calculating totals by balance fields

BalanceAndTurnovers virtual tables of accumulation registers and accounting registers allow you to get balances for a desired period. 1C:Enterprise query language provides automatic calculation of totals for groupings by balance fields. This article covers how totals by balance fields are calculated and demonstrates some of the specifics of such calculations.

Totals by balance fields

To calculate totals by a balance field, it is sufficient to get the field in a query selection list and specify that the totals should be calculated by this field.

Example:

SELECT
    Warehouse AS Warehouse,
    Items AS Items,
    Period AS Period,
    QuantityOpeningBalance AS QuantityOpeningBalance,
    QuantityTurnover AS QuantityTurnover,
    QuantityClosingBalance AS QuantityClosingBalance
FROM
    AccumulationRegister.ItemsAccounting.BalanceAndTurnovers(, , Week, , ) AS ItemsAccountingBalanceAndTurnovers
TOTALS
    SUM(QuantityOpeningBalance), SUM(QuantityTurnover), SUM(QuantityClosingBalance)
BY
    Warehouse,
    Period,
    Items

During the query execution the platform automatically identifies the QuantityOpeningBalance field as the one that contains the opening balance and the QuantityClosingBalance field as the one that contains the closing balance, and then applies the algorithm to calculate the totals by these fields.

Algorithm of calculating totals by balance fields

For a query to be able to calculate the totals by the balance fields, the query should get both values of the balance for a period: the opening and closing balances. When a query gets only one of the two balances and this value is used to calculate the totals, the platform implicitly supplements the query by get the field with the other balance.

The algorithm for calculating totals by balances consists of three steps:

1. calculation of totals for a grouping obtained before grouping by period;
2. calculation of totals for the grouping by period;
3. calculation of totals for a grouping obtained after grouping by period.

When totals are calculated for a grouping obtained before grouping by period, records are sorted by the first-level fields that have not been grouped yet and by the period field, which is followed by records iteration. The first records for a combination of the first-level fields are used to sum up the opening balance while the last records are used to calculate the closing balance.

When totals are calculated for the grouping by period, records are sorted by period, records for each date are iterated, and the sum is calculated for various combinations of the first-level field values (those that are not obtained using .(dot) from another field in a selection list) that have not yet been used to get a selection for the date that is equal to or greater than that calculated for the opening balance and less than or equal to the one calculated for the closing balance. Keep in mind that when a field from a linked table is selected for a query result, the balances for the period are calculated taking into account the combinations of values for this field.

When totals are calculated by a grouping inside a grouping by period, the calculation is performed in the same manner as for regular fields, by simply summing them up.

Using balance fields combined with other fields

If a balance field is used in an expression, for each opening balance the platform attempts to find a similar expression that contains a closing balance. If the expression is not found, the field is implicitly added to the query.

Example:

SELECT
    Warehouse AS Warehouse,
    Items AS Items,
    Period AS Period,
    QuantityOpeningBalance * Items.PurchasePrice AS QuantityOpeningBalance,
    QuantityTurnover * Items.PurchasePrice AS QuantityTurnover,
    QuantityClosingBalance * Items.PurchasePrice AS QuantityClosingBalance
FROM
    AccumulationRegister.ItemsAccounting.BalanceAndTurnovers(, , Week, , ) AS ItemsAccountingBalanceAndTurnovers
TOTALS
    SUM(QuantityOpeningBalance), SUM(QuantityTurnover), SUM(QuantityClosingBalance)
BY
    Warehouse,
    Period,
    Items

If an expression uses multiple opening or closing balance fields, the platform does not recognize this expression as a balance expression and calculates totals by this expression using simple summing up.

Calculating totals by recorder

If you need to get totals for balances by a register recorder, note that you can only get totals by the recorder within the Period grouping. This feature is due to the fact that a recorder is an elaboration of a period. If totals by a recorder are obtained before those by a period, the resulting totals are incorrect. A similar approach applies to getting totals by row number: such totals can only be obtained within a grouping by period and recorder.

Totals by recorder attribute

It is impossible to calculate totals correctly by an attribute of a recorder. If you need to include balances with a specification of the recorder attribute in the results, you should create a grouping by recorder and only include the recorder attribute in the result, without including the recorder itself.

Catalog field contents

Attributes of an hierarchical catalog that have the Use property set to For item contain NULL values in the records that are groups. Similarly, those attributes that have the Use property set to For folder contain NULL in the records that are catalog items. Keep this in mind while writing queries to catalogs.

Example:

SELECT
 Items.Description, 
 Items.IsFolder, 
 Items.StockNumber, 
 CASE 
  WHEN (Items.StockNumber) IS NULL 
   THEN "NULL" 
  ELSE "NOT NULL" 
 END 
FROM 
 Catalog.Items AS Items

Result:

Name	IsFolder	Stock number	Field1
Cigarettes	true		NULL
Peter I lights	false	18008	NOT NULL
Foods	true		NULL
Sweets	true		NULL
Truffles	false	SN-999999	NOT NULL
Candy canes	false	SN-6666888	NOT NULL
Gummy bears	false	SN-6666	NOT NULL
Mint chocolates	false	SN-7777	NOT NULL
Fruities	false	SN-999001	NOT NULL
Groceries	true		NULL

This example shows that, for all the group records, the value of the StockNumber attribute contains NULL.

<< Prev   Next >>