One of the most powerful tools of 1С:Enterprise 8, beyond a doubt, is a query builder. The builder is a visual tool to work with queries using which it is possible to build a query of any complexity.
Perhaps, in no one IDE I’ve seen such a convenient tool for rapid query development.

To create a new query using the builder - it is enough to call in the required module the context menu and select - “Query builder…” (to create the string variable with a query text) or “Query Wizard with result processing…” (to create an object “Query”, text or result sample).

The builder tools are distributed over the several panels.
The first panel in which the builder is opened - Tables and fields:

In the left part the existing configuration tables are listed (real and virtual). In the middle one the tables are displayed that are selected with us as the sources for query, in the right one - the selected fields of tables.
The most important buttons on the first tab:

1. Add nested query. In this case, a new empty window of query builder will be opened.
2. Edit nested query. If the source is not a nested query, the buttons is not active.
3. Delete table. In this case, all the fields of this table will be deleted from all the query parts.
4. Open dialog window to configure the virtual table parameters. Window view (namely, a set of configurable parameters) will depend on the type of virtual table. Disabled if the source is not a virtual table. For example, for virtual table “Turnovers”:

   

5. Set alias for the source table. Very useful function when working with virtual tables. Indeed, if we use as a source, for example, the table “OrdersRegisteredTurnovers”, then it can be named, let’s say, “Trn”. In this case, a query will be much easier to read. Strongly recommended!
6. Open dialog window (in fact, this will be another builder - “Random expression builder”) in which it is possible to build a computable filed using the visual tools:

   

7. Open dialog to edit existing field.
8. Delete field. In this case, this field will be automatically deleted from wherever it has been used (groupings, aliases, sorting…)

In addition, in the left bottom corner of builder there is a button “Query”. It opens a window directly with a query text:

A query text can be viewed and edited manually. TO do this, it is necessary to click the button “Edit query” on the left top. A window with query text will be available for editing. Once the text is changed, it is required to click again this button. If a new query is written correctly, the builder will recognize it automatically.

The next tab - “Links”. It appear only if there are more than one source:

• 1, 3 - “left” and “right” tables for join
• 2, 4 - select type of join (inner, left, right, full)
• 5 - if to set this checkbox, the join condition can be written manually (needed for complex conditions)
• 6, 8 - the fields that participate in the join condition
• 7 - condition in which the fields are compared

On the tab “Grouping” the query groupings are configured. All the fields must be divided into the grouping and totaled ones. For the totaled fields it is possible to set the function type.

The tab “Conditions” should not case also much difficulty. On the left there is a list of fields that can be used in the condition, and on the right - the conditions themselves:

Herewith, if to set checkbox “Arbitrary”, the condition text can be changed (needed for complex conditions).

On the tab “More” it is possible to perform the following settings:

1. “TOP” is added to the query - select only first N records.
2. Number of selected records for previous setting.
3. “DISTINCT” is added to the query - get the result without the duplicate records. DO not confuse with “Grouping”! “DISTINCT” does not sum up the excludable duplicates of rows.
4. “ALLOWED” is added to the query - return only those records for which the current user has the rights.
5. Create query for normal data sample.
6. Select data and place them into the temporary table for further usage, in this case, it is required to set the table name in the field 8.
7. Create query to delete the previously created temporary table, in this case, it is required to set the name of deleted table in the field 8.
8. The name of created or deleted temporary table.

The tab “Unions/Aliases” allows creating the join of several tables and setting the correspondence of fields between them:

In this case, the additional tabs appear on the main tabs to switch between the different query parts for joining:

The tab “Order” allows setting an order to sort the query result:

The tab “Totals” allows getting the hierarchical query result with calculated totals from the selected fields. Very similar to the “Grouping”, but with a difference - in the “Totals” it is possible to define an arbitrary set of fields as well as to set the formation of grand total. And in the “Grouping” all the query fields must be used as the grouping or totaled ones, but the grand totals cannot be calculated.

The tab “Builder” is used not so often, so we will skip it.

The tab “Query batch” allows creating in a single batch several queries at once. Frequently used with temporary tables. The principle of operation is the same as in the tab “Unions/Aliases”:

When creating the batch query, the additional tabs appear on the other tabs for convenient movement between different queries:

I hope you will be excited when you the first time try this tool!

Below there is a query that is completely composed using the Query builder:

	//{{QUERY_BUILDER_WITH_RESULT_PROCESSING
	// This fragment was built by the wizard.
	// Warning! All manually made changes will be lost next time you use the wizard.

	Query = New Query;
	Query.Text = 
		"SELECT TOP 500
		|	NestedSelect.Ref,
		|	NestedSelect.Number,
		|	SUM(NestedSelect.Count) AS Count
		|INTO Docs
		|FROM
		|	(SELECT
		|		CashPurchase.Ref AS Ref,
		|		CashPurchase.Number AS Number,
		|		1 AS Count
		|	FROM
		|		Document.CashPurchase AS CashPurchase
		|	
		|	UNION ALL
		|	
		|	SELECT
		|		CashReceipt.Ref,
		|		CashReceipt.Number,
		|		1
		|	FROM
		|		Document.CashReceipt AS CashReceipt) AS NestedSelect
		|WHERE
		|	NestedSelect.Number LIKE ""%2013%""
		|
		|GROUP BY
		|	NestedSelect.Ref,
		|	NestedSelect.Number
		|
		|ORDER BY
		|	Count DESC
		|;
		|
		|////////////////////////////////////////////////////////////////////////////////
		|SELECT
		|	Docs.Ref,
		|	Docs.Number,
		|	SUM(Docs.Count) AS Count,
		|	SUM(InventoryJrnlTurnovers.QtyTurnover) AS QtyTurnover,
		|	SUM(InventoryJrnlTurnovers.AmountTurnover) AS AmountTurnover
		|FROM
		|	AccumulationRegister.InventoryJrnl.Turnovers(, , Recorder, ) AS InventoryJrnlTurnovers
		|		INNER JOIN Docs AS Docs
		|		ON InventoryJrnlTurnovers.Recorder = Docs.Ref
		|WHERE
		|	InventoryJrnlTurnovers.Location = &Location
		|
		|GROUP BY
		|	Docs.Ref,
		|	Docs.Number
		|TOTALS
		|	SUM(Count),
		|	SUM(QtyTurnover),
		|	SUM(AmountTurnover)
		|BY
		|	OVERALL";

	Query.SetParameter("Location", Location);

	QueryResult = Query.Execute();

	SelectionDetailRecords = QueryResult.Select();

	While SelectionDetailRecords.Next() Do
		// Insert selection processing SelectionDetailRecords
	EndDo;

	//}}QUERY_BUILDER_WITH_RESULT_PROCESSING