Queries

The query mechanism is one of the platform's methods of accessing data. It enables developers to read and process data stored in an infobase; at that, queries can not change the data. The role of queries is to quickly obtain and process data sets acquired from large arrays of data in a database.

Tabular Way to Access Data

Queries access data stored in a database via tables. It means that all data is presented as a collection of interconnected tables that can be accessed either individually or as several interrelated tables:

This way of handling data allows compiling complex data sets, grouped and sorted in a certain way. Also, the platform can calculate subtotals and totals for such data sets. Where necessary, it is possible to limit the number of records in a dataset, filter records by content, and similar.

Query Language

The role of the query language is to let developers use own algorithms in queries. Though the language has SQL at its core, it is tailored to minimize application development efforts and contains a significant number of extensions that handle financial and economic tasks. Here is the list of the most significant features offered by the query language:

Accessing Fields via Dot Syntax (".")

If fields in a table are of type Ref. (they store references to objects of another table), developers can refer to them in the query text through a dot symbol ("."), and the number of nesting levels is not limited by the platform.

Accessing Nested Tables (Tabular Sections in Documents and Catalog Elements)

Inside the platform, we can access nested tabular sections both as individual tables or just specific table fields. For example, when accessing document Sales Inventory (containing tabular section Products with the list of shipped products), we can treat the tabular section as an individual table:

Alternatively, we can access the document header record, where the value for field Products are all records of the nested table subordinate to the respective object (document):

Automatic Ordering

When developers wish to select the most appropriate ("natural") order for displaying information on the screen or in a report, in most cases it is enough to set the automatic ordering mode.

Multidimensional and Multilevel Generation of Totals

The platform calculates totals and subtotals with the account of groups and hierarchy, picking levels to calculate subtotals in an arbitrary order, thus correctly calculating and displaying totals for specified periods.

Virtual Tables Support

Virtual tables created by the platform contain data that applications can utilize with little or no modification and without the need for composing complex queries. For example, such a virtual table can provide data on available stock per specific period or moment. At the same time, virtual tables utilize stored information, for example, previously calculated totals and similar.

Standard SQL Operations

The query language supports standard SQL operations such as UNION, JOIN, and others.

Temporary Tables

Thanks to the query language, developers can utilize temporary tables in queries. These tables can improve the performance of queries, reduce the number of locks, and make query texts easier to read.

Assume we need to get data from two accumulation registers. We put the data from one register into a temporary table:

A query using a temporary table looks like this:

Batch Queries

Due to the support of batch queries in the query language, it is very easy to work with temporary tables. Thus, we can combine in a single query the process of creating a temporary table and using the table to get the required result. A batch query is a sequence of queries separated by the ";" sign. The platform runs queries one by one. Depending on the method used, the batch query output is either the result of the last query in the batch or an array of the results from all queries in the order the queries are listed in the batch.

Query Wizards

To facilitate the work of developers, the platform offers two special-purpose wizards. They help developers compose the correct text of a query using only visual aids. By selecting the required fields in tables with a mouse, developers can create queries even without knowing the syntax of the query language.

• The Query Wizard is designed only for composing query texts. More…

• The Query Wizard with Result Processing, in addition to the query text, creates a piece of code that runs the query and displays the results in a spreadsheet document or chart. More…

Query Console

The Query Console allows configuration developers and deployment specialists to debug queries and view query output in 1C:Enterprise mode. More…