Queries in 1C:Enterprirse 8 are used to fetch information from databases. In fact, a query is a request to the system to select certain information from a database. Often some additional operations with data are requested: data sometimes needs to be grouped, filtered, or calculated. For instance, you can use queries to select all the employees in a specific job position or find out the sales turnover by item during a given year, detailed up to the month.
1C:Enterprise query language is an SQL-based language with a large number of extensions that reflect the specifics of financial and economic tasks and greatly simplify business application development.
Among the most useful options are the following:
- Dereferencing of reference fields, i.e., calling fields via a period ("."). If table fields have a reference type and store references to the objects from another table, the developer may reference them via a period in his or her query. It is important to note that the system does not limit the number of nested levels of such references.
- Calling nested tables (tabular sections of documents and reference elements). The system supports calling nested tabular sections as if they were separate tables or whole fields of a single table. For instance, if we call an invoice (a document that contains a tabular section with the description of the inventory shipped), we can work with a tabular section as a separate table (and obtain records from different objects), or consider the heading entry as an invoice itself where field values are all entries of a nested table that are subordinate to this object.
- Automatic sorting. To specify the optimal order of data on the screen or in the report, in most cases the developer can simply set automatic orders.
- Multidimensional and multilevel totals formation. Totals and subtotals are formed with groups and hierarchies being taken into account. Levels can be iterated arbitrarily with a subtotals calculation and correct totals calculation by time dimensions.
- Virtual tables support. The virtual tables provided by the system can be used to obtain complete data for most business tasks without any need to create complex queries. For instance, such a virtual table may present data on inventory balances for different periods as of a particular moment in time. At the same time, virtual tables use the stored information as much as possible, i.e., previously calculated totals, etc.
- Of course, standard SQL operations, such as Union, Join, etc. are supported in 1C:Enterprise query language.
