Bitness of results of expressions and aggregate functions in query language

When queries use data operations and functions with a definition that includes specification of length (String, Number) and accuracy (Number), you might need to keep in mind the rules for calculating the length and accuracy of results. This section covers these rules.

String operations

Description of the String data may include the following additional specifications: length (maximum number of characters), allowed length (fixed or variable), unlimited length (False or True). This table lists the rules for calculating additional specifications of the function results based on additional specifications of their string operands.

Function

Unlimited length

True

False, length = n

Fixed

Variable

SUBSTRUNG(String, m, k)  Unlimited length Length k, variable Length k, variable
MIN(String)  Unlimited length Length n, fixed Length n, variable
Max(String)  Unlimited length Length n, fixed Length n, variable

The following table covers the rules used to determine additional specifications of the "+" operation (concatenation of strings) that takes two string operands.

Operand 1

Operand 2

Unlimited length

Length = m

Fixed

Variable

Unlimited length Unlimited length Unlimited length Unlimited length
Length n Fixed Unlimited length Length n + m, fixed Length n + m, variable
Variable Unlimited length Length n + m, variable Length n + m, variable

Operations with numbers

When you perform various conversions of numeric data, keep in mind that a number can contain (integer and fractional parts combined) a maximum of 38 digits. 1C:Enterprise uses decimal numbers with a fixed point. This means that the number 34.28 contains 4 digits, and number 0.000000001 has 9 digits (the 0 before the period is not considered to be a digit), while the number 3200000000 has 10 digits. Bitness of the results of various operations is calculated to exclude possible arithmetical overflows and at the same time to get the most accurate result. During configuration development, the selection of bitness of metadata object fields should be based on possible bitness of the data stored in such fields and should not take into account any possible bitness increase due to arithmetical operations and functions.

"+" (addition), "-" (subtraction)

Addition and subtraction operations are always executed precisely in the query language. For this, the length of the fractional part of the result is determined as the maximum length of fractional parts of all operands. The length of the integer part of the result is defined as the maximum length of the integer parts of operands plus 1. If in this case the total number of digits in the integer and fractional parts exceeds 38, the number of digits is reduced to 38 by reducing the number of digits in the fractional part, but to no less than 10.

"*" (multiplication)

Multiplication operations are executed precisely if at least one of the operands is an integer. The length of the fractional part of the result is determined as the maximum length of the operand fractional parts, but no less than 10 digits. The length of the integer part of the result is calculated as a sum of the lengths of the integer parts of the operands. If the total number of digits in the integer and fractional parts exceeds 38, it is reduced to 38.

"/" (division)

The division operation is only executed accurately if both operands are integers and the first operand can be divided by the second one without anything left over. The length of the fractional part of the result is determined as the maximum length of the operand fractional parts, but no less than 10 digits. The length of the integer part of the result equals the sum of the lengths of the integer part of the dividend and fractional part of the divisor. If in this case the total number of digits in the integer and fractional parts exceeds 38, this number is reduced to 38 by reducing the number of digits in the fractional part, but to no less than 10.

SUM

The aggregate function SUM is calculated as precisely as possible. The number of fractional digits of the result equals the number of fractional digits of the operand. The number of digits in the integer part is increased by 7. If in this case the total number of digits in the integer and fractional parts exceeds 38, this number is reduced to 38 by reducing the number of digits in the fractional part, but to no less than 10.

MIN, MAX, AVG

The results of MIN, MAX, and AVG functions have the same number of digits in the integer and fractional parts as their operands.

COUNT, YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, DATEDIFF 

The listed functions have a numeric result with 10 digits in the integer part. There is no fractional part.

NOTES:
  1. The total number of digits in a number equals the sum of the numbers of digits in its integer and fractional parts. For example, if following the rules above the result has 4 digits in the integer part and 3 digits in the fractional part, its type description is Number(7, 3)
  2. In the client/server mode, the number of digits in the fractional part of the division operation ("/") is limited by 6 instead of 10. This is due to the specifics of decimal arithmetic in Microsoft SQL Server.
  3. If you find it reasonable to change the calculation rules for length and result accuracy for operations with data, use the CAST operator to specify the required length and accuracy. Note that if you want to change the length and accuracy of an operation result, the CAST operator should be applied to its operand. In this case the length and accuracy of the operation result is calculated using the rules above based on the length and accuracy of the operand specified by CAST. For example, if the field "Attribute1" is of type "Number(8, 0)", for the expression Attribute1 / 3 to have 15 digits in the fractional part, use the following code: CAST(Attribute1 AS Number(23, 15)) / 3

Next page: Calculating totals by balance fields

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.