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.
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);