So, this is our query language, and we’re gonna start from the top as ever. This would be the SELECT clause. The SELECT clause tells the query what columns are to be in the resulting recordset, and it’s the only clause the query does not know how to live without. It means (among other things) that we can use literals here.
But the fields are what we use much more often here. It can be the asterisk wildcard, a Metadata Object field name, or a Metadata Object field dereferencing. There are also all kinds of expressions in store for us. We can use aggregates, functions of all stripes and colors, the CASE operation, as well as every arithmetical and logical operation in the book. So, let’s see who does what.
First things first. Literals. The idea is perfectly simple. Just use any literal right after SELECT and it will end up in the results exactly as you put it. We didn’t tell the SELECT clause how to name the column, so it came up with this creative solution, but that’s easily fixable. I’m just adding AS and then the column name I want, and here we go. What do we do if we need more than one column? Easy. We just list them separated by a comma like this. And this is our result. Next up: Fields.
To output fields query needs to know where to take them from. So, I’m telling it to read the Sales documents. And this is how to read all the fields there are. This asterisk is called a wildcard, and this is how it works. It has read all the Sales documents and output all their fields including the content of the Products tabular part. Usually, we don’t want all of the fields, in which case we just list only the field names we need just like this. And this will be our result.
If the field has a reference type, we can dereference it. Let me show you. This is a list of all products from all Sales documents. There are the same products in different documents, so I’m getting rid of duplicates like this. And now I want to follow these references to the Products catalog and get its fields’ values using this dot-syntax dereferencing.
This Brand field is another reference, so I can go to the Brand catalog and get its fields as well. And this is my result. And, of course, if I’m not completely happy with the column names the Platform came up with, I can always rename them just like this. So, these were our fields.
Next: aggregates. These guys live up here and work only paired up with the GROUP BY clause. So, I’m telling the Platform that it should group all similar products into one record and count up the number of this product occurrences for each group. And this is what I get. Which does not make a lot of sense, honestly, so let me sum up the overall products quantities across all documents instead. And this is my result. These were our aggregates.
Next up: functions. They live down here and, as you can see, they are plentiful indeed. In a nutshell, they just duplicate the 1C script system functions at the query level. Here is a simple example to give you an idea. This is the list of my cashiers, and now I want to get their initials and their year of birth. So this is what I do.
I’m using the Substring function to cut off the first symbol of the first and last names. And then the Year function to get the year out of the birth date. And here we go. So these were the functions.
OK, what else do we have here? The Case operation. Let me bring back this Cashiers query because I’ve just been told that we need a query checking the age of our cashiers, so we can tell if they can buy alcohol or not. So, this is what I do.
First, I’m using this Datediff function to calculate the age of each cashier. There is no CurrentDate function in the query language, so I’m passing the current date as a parameter behind the scene. And this is what I get. And now I’m using the CASE operation to tell the query to output "Yes" if the cashier is older than 21 and output "No" otherwise. And this is the result.
As for the binary operations, we already used one right here in this query. We can use all kinds of operations requiring two operands (this is where the "binary" came from). This includes the strings concatenation, all arithmetical operations on numerical and stuff like that.
We can also use all kinds of logical operations, and here is an example for you. Instead of using CASE in this case (no pun intended), I can simply ask the query to calculate this logical expression. And guess what. The result will be exactly the same. So, this was our SELECT clause.