Lesson 3-2

<Expression> [NOT] IN (<List or Subquery>)

Using this condition type we can check if the expression is in the list or the resulting recordset of a subquery. Using the list form we could rewrite the balance checking query like this:

SELECT
 BalanceOfMaterialsBalance.Material AS Material,
 BalanceOfMaterialsBalance.Warehouse AS Warehouse,
 BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
FROM
 AccumulationRegister.BalanceOfMaterials.Balance AS BalanceOfMaterialsBalance
WHERE
 QuantityBalance < 0 AND
 Warehouse = &Warehouse AND
 Material IN (&Material1, &Material2, &Material3,  ...)

This query does its job, but it has to be dynamically built and have the same issues: the query text is massive, and it needs to be often recalculated. Using subquery form of this syntax is a much better choice. Here is a basic idea behind this condition type:

SELECT <Something>
FROM <Somewhere>
WHERE <SomeField> IN (
	SELECT <SomethingElse>
	FROM <SomewhereElse>
	WHERE <AnotherCondition>
)

So we can embed the subquery inside another query’s WHERE clause and select only those records from <somewhere> that have <some field> value containing in the resulting recordset of the subquery. The subquery itself is a regular query that can use any kind of conditions, including “IN (sub-sub-query)”, so actually you can have as many levels of subqueries’ nesting as you need.

The good thing about this condition type is that we can use a static text for the query doing what we need. Let’s see how we can do it.

Here is a resulting source code we’ve got:

 //3. Checking if there any negative balance records
 Query = New Query;
 Query.Text = 
  "SELECT
  | BalanceOfMaterialsBalance.Material AS Material,
  | BalanceOfMaterialsBalance.Warehouse AS Warehouse,
  | BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
  |FROM
  | AccumulationRegister.BalanceOfMaterials.Balance AS BalanceOfMaterialsBalance
  |WHERE
  | BalanceOfMaterialsBalance.QuantityBalance < 0
  | AND BalanceOfMaterialsBalance.Warehouse = &Warehouse
  | AND BalanceOfMaterialsBalance.Material IN
  |   (SELECT
  |    ServicesMaterialsAndServices.MaterialOrService
  |   FROM
  |    Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
  |   WHERE
  |    ServicesMaterialsAndServices.Ref = &Ref
  |    AND ServicesMaterialsAndServices.MaterialOrService.MaterialOrService = 
  |    &MaterialOrService)";
  
 Query.SetParameter("Warehouse", ThisObject.Warehouse);
 Query.SetParameter("Ref", ThisObject.Ref);
 Query.SetParameter("MaterialOrService", Enums.MaterialOrService.Material);

So, we ended up with one static query using a subquery as a condition.

Here is one more thing about this condition types we need to consider. Here is our current Services document structure:

Note that the Warehouse attribute is in the document “header”, whereas the MaterialOrService attribute is in the tabular section. It means that a document can contain any number of different materials but only one warehouse. In other words, we have to get all materials for one service from one warehouse.

Therefore, we check the warehouse against the only parameter value but in the same query we check the material against the list of different values.

Now, what if we need to extend our app’s functionality in a way that we can grab materials from different warehouses for a single service?

In this case, we need to change the Services document structure like this:

Here we moved the Warehouse attribute to the tabular section so that materials could be requested from a different warehouse within a single document.

Now, how we change the checking balance query in order for it to work properly in this case? We need to check the warehouse against a warehouses list, but what syntax we can use here? The most obvious solution looks like this:

SELECT
 BalanceOfMaterialsBalance.Material AS Material,
 BalanceOfMaterialsBalance.Warehouse AS Warehouse,
 BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
FROM
 AccumulationRegister.BalanceOfMaterials.Balance AS BalanceOfMaterialsBalance
WHERE
 BalanceOfMaterialsBalance.QuantityBalance < 0
 AND BalanceOfMaterialsBalance.Material IN
   (SELECT
    ServicesMaterialsAndServices.MaterialOrService
   FROM
    Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
   WHERE
    ServicesMaterialsAndServices.Ref = &Ref
    AND ServicesMaterialsAndServices.MaterialOrService.MaterialOrService = 
     &MaterialOrService)
    AND BalanceOfMaterialsBalance.Warehouse IN
   (SELECT
    ServicesMaterialsAndServices.Warehouse
   FROM
    Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
   WHERE
    ServicesMaterialsAndServices.Ref = &Ref)

Here we just added another warehouse checking subquery by analogy with materials checking subquery and combined these two with logical AND.

Will this query return the result we need? Let’s check it out. Say, we have tabular section consisting of these two lines:

In this case the condition above can be represented as:

Material IN (M1, M2) AND Warehouse IN (W1, W2)

of:

(Material = M1 OR Material = M2) AND (Warehouse = W1 OR Warehouse = W2)

As you probably know, logical operators AND and OR work as multiplication (*) and addition (+) correspondently in a sense that they conform to the arithmetical parentheses removing rules. So if we rewrite the condition as:

(M1 + M2) * (W1 + W2)

and remove the parentheses, we will get this:

M1*W1 + M1*W2 + M2*W1 + M2*W2

Transforming it back to logical operators form we will get the following condition:

(Material = M1 AND Warehouse = W1) OR

(Material = M1 AND Warehouse = W2) OR

(Material = M2 AND Warehouse = W1) OR

(Material = M2 AND Warehouse = W2)

In other words, with this condition we will get the following records of the Balance table:

So, we get two records we need but we also get two records that have nothing to do with our document.

OK, this condition also gives us unnecessary records. What can we possibly do to fix it?

If you are familiar with general SQL syntax, you probably know that you can use only single-valued expressions in a condition like <expression> IN <subquery>, so you must use other types of condition to resolve the issue (namely - a JOIN that will be discussed a little bit later).

But with 1C:Enterprise (that offers some extensions for SQL syntax) you can use IN condition to compare a set of values against a multifield subquery recordset. This form of IN condition syntax looks like this:

(<exp1>, <exp2>,...) IN (SELECT <exp1>, <exp2>,...)

After moving the Warehouse attribute into the tabular section and implementing this extended IN condition syntax we will get the following Posting handler procedure:

Procedure Posting(Cancel, Mode)
 
 // 1. Filling out BalanceOfMaterials recordset
 RegisterRecords.BalanceOfMaterials.Write = True;
 For Each CurRowMaterialsAndServices In MaterialsAndServices Do
  If CurRowMaterialsAndServices.MaterialOrService.MaterialOrService =
   Enums.MaterialOrService.Service Then Continue;
  EndIf;
  Record = RegisterRecords.BalanceOfMaterials.Add();
  Record.RecordType = AccumulationRecordType.Expense;
  Record.Period = Date;
  Record.Material = CurRowMaterialsAndServices.MaterialOrService;
  Record.Warehouse = CurRowMaterialsAndServices.Warehouse;
  Record.Quantity = CurRowMaterialsAndServices.Quantity;
 EndDo;
 
 //2. Writing down BalanceOfMaterials recordset
 RegisterRecords.BalanceOfMaterials.LockForUpdate = True;
 RegisterRecords.Write();
  
 //3. Checking if there any negative balance records
 Query = New Query;
 Query.Text = 
  "SELECT
  | BalanceOfMaterialsBalance.Material AS Material,
  | BalanceOfMaterialsBalance.Warehouse AS Warehouse,
  | BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
  |FROM
  | AccumulationRegister.BalanceOfMaterials.Balance AS BalanceOfMaterialsBalance
  |WHERE
  | BalanceOfMaterialsBalance.QuantityBalance < 0
  | AND (BalanceOfMaterialsBalance.Warehouse, BalanceOfMaterialsBalance.Material) IN
  |   (SELECT
  |    ServicesMaterialsAndServices.Warehouse,
  |    ServicesMaterialsAndServices.MaterialOrService
  |   FROM
  |    Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
  |   WHERE
  |    ServicesMaterialsAndServices.Ref = &Ref
  |    AND ServicesMaterialsAndServices.MaterialOrService.MaterialOrService = 
  |    &MaterialOrService)";
  
 Query.SetParameter("Ref", ThisObject.Ref);
 Query.SetParameter("MaterialOrService", Enums.MaterialOrService.Material);
  
 //4. Execute the query
 QueryResult = Query.Execute();
 SelectionDetailRecords = QueryResult.Select();
 
 //5. If the result is not empty, cancel the document's posting
 If SelectionDetailRecords.Count() = 0 Then
  Return;
 EndIf;
 Cancel = True;
 
 //6. Prepare the error message and display it.
 MessageText = 
  "There is not enough materials. See the list of deficits below:" +
  MessageText;
 While SelectionDetailRecords.Next() Do
  MessageText = 
   MessageText + Chars.CR + Chars.LF + 
   SelectionDetailRecords.Warehouse.Description + "\" + 
   SelectionDetailRecords.Material.Description + ": " +
   SelectionDetailRecords.QuantityBalance;
 EndDo;
 Message(MessageText);
 
EndProcedure

 Lesson 3-1 | Course description | Lesson 3-3