1Ci Support Help Center home page
Submit a request
Sign in
  1. 1Ci Support
  2. 1C:Enterprise Development Standards
  3. Data processing
  4. Working with queries

Creating query texts

  • Working with queries
    • Creating query texts
    • Multiple execution of the similar queries
    • Checking for an empty query result
    • Restriction on the use of FULL EXTERNAL CONNECTION structure in queries
    • Using UNION and UNION ALL words in queries
    • Ordering query results
    • Rounding arithmetic results in queries
    • Specifics of using LIKE operator in queries
    • Data source aliases in queries

Scope: managed applications, mobile applications, and ordinary applications.

1. Always use UPPERCASE for query keywords to make them stand out from the rest of the query text.

Best practices

2. It is recommended that you specify optional query elements. Particularly, introduce explicit aliases to make the query code more robust and human-readable. For example, a query contains a field that is declared as

CashAccount.Currency

When the attribute name is edited, you will have to edit the query text and the code that addresses the query result by the property name (Currency). If the field is declared as

CashAccount.Currency AS Currency

when the attribute name is edited, you will have to edit only the query text.

2.1. Pay attention to aliases that automatically assigned to pivot fields. For example, the field "... CashAccount.Currency.Description...". will be automatically introduced as CurrencyDescription, not Description.

2.2. Always embed the AS operator before the source field alias.

3. Mind the query structure. Use conventional style guides. Do not write query text in one line. When you write a query, keep in mind that other developers can read it later. So keep query texts as clean and readable as possible.

4. Leave some comments, especially in complex queries, which might contain multiple nested queries, unions, and joins. A good comment is the one that makes the query structure more graspable for other developers.

Please note that when you create a query with Query Wizard, it deletes all your comments without warning.

5. When you create a Query object, it is recommended that you leave a comment about the query purpose.

6.1. When a query is generated programmatically, in few steps, leave a comment explaining each step.

6.2. Make sure that each part of the generated query can be opened in Query Wizard. This will help you to

  • validate query syntax, and
  • simplify further code maintenance and development.

Below you can find some common query modification scenarios.

Changing name of table or selection field

Incorrect:

QueryText =

"SELECT
| Products.Description  AS Description,
| Products. " + FieldNameCode + " AS CodeSKU
|FROM
| Catalog.Products AS Products";


Correct:

QueryText =
"SELECT
| Products.Description  AS Description,
| &FieldNameCode  AS CodeSKU
|FROM
| Catalog.Products AS Products;

QueryText = StrReplace(QueryText, "&FieldNameCode ", "Products." + FieldNameCode);

The same for a table:

QueryText =

"SELECT
| CatalogTable.Description  AS Description,
| CatalogTable.Code  AS Code
|FROM
| &CatalogTable AS CatalogTable";
QueryText = StrReplace(QueryText, "&CatalogTable", "Catalog." + CatalogName);

Another way to change a table's name:

QueryText =

"SELECT
| Products.Description  AS GoodsDescription,
| ISNULL(StockTable.AvailableStock,0) AS StockBalance
|FROM
| Catalog.Products AS Products
| LEFT JOIN #StockTable AS StockTable
| BY Products.Ref= StockTable.Products";

If BinLocationUsed Then
 QueryText = StrReplace(QueryText, "#StockTable", "AccumulationRegister.StockInBins.Balance");
Else
 QueryText = StrReplace(QueryText, "#StockTable", "AccumulationRegister.Stock.Balance");
EndIf;

Concatenating multiple queries

Incorrect:

QueryText = " ";

If UsePackaging Then

QueryText =

"SELECT
| Packaging.Ref AS Ref
|FROM
| Catalog.Packaging AS Packaging;
|/////////////////////////////////////////////////////////////
|";

EndIf;

QueryText = QueryText +
"SELECT
| Products.Ref AS Ref
|FROM
| Catalog. Products AS Products";

Correct:

QueryText = " ";

If UsePackaging Then

QueryText =

"SELECT
| Packaging.Ref AS Ref
|FROM
| Catalog.Packaging AS Packaging";

QueryText = QueryText +
"
|;
|/////////////////////////////////////////////////////////////
|";

EndIf;

QueryText = QueryText +
"SELECT
| Products.Ref AS Ref
|FROM
| Catalog.Products AS Products";

Another way to do the same:

Separator =
"
|;
|/////////////////////////////////////////////////////////////
|";

BatchQuery = New Array;

QueryText =
"SELECT
| Packages.Ref AS Ref
|FROM
| Catalog.Packages AS Packages";

BatchQuery.Add(QueryText);

QueryText =
"SELECT
| Products.Ref AS Ref
|FROM
| Catalog.Products AS Products ";

BatchQuery.Add(QueryText);
QueryText = StrConcat(BatchQuery, Separator);

See also:

  • Localization requirements: queries, dynamic lists, and DCS reports.
© 2020 1C INTERNATIONAL LLC www.1Ci.com Support policy