The 1C:Enterprise query language syntax is based on a well-known and universally-popular SQL language. Let’s see what Wikipedia has to say about this guy. "SQL: The Structured Query Language designed for managing data held in a relational database management system." So, the first and foremost feature of this language is that it’s structured. So let’s see, what kind of structure this is.
Let me show you another place where you can find 1C script language references, including 1C query language complete syntax and stuff like that. I’m going to Help, help content, and here is the Script section, which is an exact copy of the Syntax Assistant only if in a different order. OK, let’s see what we’ve got in the query department. So, this is our query section, and this is the query text syntax right here. The first thing that sticks out for me is that we don’t have much in terms of SQL here. SELECT statement, and a few more rather marginal ones, and that’s it. Where is everybody? Isn’t it supposed to be much more than that? So, let’s compare the complete SQL syntax with 1C query language syntax.
Generally speaking, SQL consists of two major parts: the Data Definition Language and the Data Manipulation Language The DDL one is all about working with the metadata. It knows how to declare tables, delete tables, change table structure and stuff like that. The DML one is about working with the data. It can do everything it takes to have full control over the data: read them add new records to tables, edit them, and delete them.
As for the 1C query language, there is no way to work with the metadata using it. Instead, we do this by specifying the Metadata Objects’ structure, as you all vividly remember, of course. As for the data manipulation, we can read data using 1C Query language, but no adding, editing or deleting for us here. This is what we use the Metadata Objects’ methods for, of course. So, the only thing we will be talking in this Module is the SELECT statement, so let’s see what we’ve got here.
So, this is our SELECT statement. Its sole purpose is to retrieve data from the database, process them and present the result in a table form. Inside of it, we have the SELECT clause, that defines what columns are to be in the query result. Next down is the FROM clause. This one tells the query what table to take the data from. If we want columns from some other tables to join the party, we use this JOIN thingy and it grabs, for example, the Inventory balance records and adds it to the Sales documents tabular section records.
OK, next down: the WHERE clause. This one filters out the records that we don’t need for whatever reason. Then, we have the GROUP BY clause that aggregates the results: for example, sums up the similar Products’ quantities like this. During this process, we need to sacrifice the fields not participating in aggregates, so no Document number and QuantityLeft for us anymore. The HAVING clause filters out result records after they are aggregated. And the ORDER BY section sets the order the result records go in. So, this is our SELECT statement structure. At least, the part of it we’re going to look at in this Module.