1Ci Support Help Center home page
Submit a request
Sign in
  1. 1Ci Support
  2. 1C:Enterprise Development Standards
  3. Data processing
  4. Working with queries

Restriction on the use of FULL EXTERNAL CONNECTION structure in queries

  • Working with queries
    • Creating query texts
    • Multiple execution of the similar queries
    • Checking for an empty query result
    • Restriction on the use of FULL EXTERNAL CONNECTION structure in queries
    • Using UNION and UNION ALL words in queries
    • Ordering query results
    • Rounding arithmetic results in queries
    • Specifics of using LIKE operator in queries
    • Data source aliases in queries

Scope: managed applications, mobile applications, and ordinary applications.

Best practices

1.1. With PostgreSQL databases, the server performance might suffer if you use the FULL OUTER JOIN clause in your queries. The impact is more significant when the query contains a few of such clauses.

That's why it is not recommended that you use FULL OUTER JOIN in queries. Whenever possible, reconsider the query text to replace this clause with an equivalent.

For example, the query

SELECT
ISNULL(SalesPlan.Products, ActualSales.Products) AS Products,
ISNULL(SalesPlan.Sum, 0) AS SumPlan,
ISNULL(ActualSales.Sum, 0) AS ActualSum
FROM
SalesPlan AS SalesPlan
  FULL JOIN ActualSales AS ActualSales
  BY SalesPlan.Products = ActualSales.Products

can be replaced with the following query that doesn't contain FULL OUTER JOIN:

SELECT
TargetActualSales.Products AS Products,
SUM(TargetActualSales.SumTarget) AS SumTarget,
SUM(TargetActualSales.SumActual) AS SumActual
FROM
 (SELECT
  SalesPlan.Products AS Products,
  SalesPlan.Sum AS SumPlan,
  0 AS SumActual
FROM
  SalesPlan AS SalesPlan
 
UNION ALL
 
SELECT
  ActualSales.Products,
  0,
  ActualSales.Sum
FROM
  ActualSales AS ActualSales) AS TargetActualSales

GROUP BY
TargetActualSales.Products

1.2. You can ignore the above mentioned recommendation and use FULL OUTER JOIN only when other clauses cannot deliver the required result. Note that when you execute a query in PostgreSQL, 1C:Enterprise substitutes FULL OUTER JOIN with an equivalent clause. All query attributes, such as TOP, DISTINCT, and ORDER BY, remain intact. In this scenario, there is no need to get rid of FULL OUTER JOIN in your query.

2. Do not access tabular sections in the SELECT statement and use FULL JOINT in the same query.

This requirement is based on some specifics of executing such queries in PostgreSQL and migrating databases to this DBMS.

See also:

  • General requirements for configurations
© 2020-2021 1C INTERNATIONAL LLC www.1Ci.com Support policy