Creating query texts

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: