Plataforma 1C:Enterprise >> Preguntas frecuentes >> Desarrollo

Lenguaje de consulta

Selección de todos los valores pertenecientes a un grupo de un resultado de consulta
Visualización de campos de referencia
Complementar el resultado de la consulta con los días pertenecientes a un período especificado
Uso de filtros en consultas con tablas virtuales
Uso de la cláusula FOR UPDATE en el lenguaje de consulta
Uso de expresiones personalizadas en totales del lenguaje de consulta
Ordenamiento en consultas anidadas
Uso de la función ISNULL()
Detalles sobre cómo usar el operador de inclusión de grupo
Aspectos específicos del uso de la cláusula GROUP BY
Aspectos específicos del trabajo con la tabla virtual de datos de programación del registro de cálculo
Aspectos específicos del trabajo con el campo Presentación y la función Presentation() del lenguaje de consulta
Aspectos específicos de la conexión a la tabla de saldos virtuales
Aspectos específicos de la clasificación por campos de referencia
Aspectos específicos de la función DATEDIFF del lenguaje de consulta
Aspectos específicos de la ejecución de la función SUBSTRING() del lenguaje de consulta
Aspectos específicos del uso de la cláusula “IN HIERARCHY” en el lenguaje de consulta
Acceso a campos desde otros campos usando . (punto)
Consultas de ejemplo para operaciones con catálogos jerárquicos
Precisión de los resultados de expresiones y funciones de agregado en el lenguaje de consulta
Cálculo de totales por campos de saldo
Contenido de campos de catálogo

Selección de todos los valores pertenecientes a un grupo de un resultado de consulta

Cuando se itera a través de un resultado de consulta, a menudo es necesario obtener todos los valores de agrupación dentro de otro grupo. Por ejemplo, puede que necesite esto para mostrar una tabla dinámica en un informe. Puede implementar esto utilizando el tercer parámetro de la función Choose() del objeto QueryResultSelection.

Consideremos un ejemplo. Supongamos que necesitas generar una tabla dinámica de saldos de productos en múltiples almacenes. Los productos deben mostrarse en filas mientras que los almacenes deben representarse en columnas. Una consulta que obtiene los saldos tiene el siguiente formato:

SELECT
    ItemAccountingBalance.Items AS Artículos,
    ItemAccountingBalance.Items.Presentation,
    ItemAccountingBalance.Warehouse AS Almacén,
    ItemAccountingBalance.Warehouse.Presentation,
    ItemAccountingBalance.QuantityBalance AS SaldoCantidad
FROM
    AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
TOTALS SUM(SaldoCantidad) BY 
    OVERALL,
    Artículos,
    Almacén

Para iterar a través de los productos, utiliza la selección del resultado de la consulta:

ItemSelection = Result.Choose(QueryResultIteration.ByGroups, "Artículos");
While ItemSelection.Next() Do
    ItemsArea.Parameters.Fill(ItemSelection);
    SpreadsheetDoc.Put(ItemsArea);

Para iterar a través de los productos en todos los almacenes disponibles en el resultado de la consulta, obtén una selección anidada basada en la selección de productos y especifica Todos para el tercer parámetro:

WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Almacén", "Todos");
While WarehouseSelection.Next() Do
    WarehouseArea.Parameters.Fill(WarehouseSelection);
    Spreadsheet.Join(WarehouseArea);
EndDo;

Si necesitas generar múltiples tablas dinámicas independientes para cada valor de agrupación, en el tercer parámetro de selección especifica el nombre de la agrupación, de modo que la consulta recupere los valores pertenecientes a la agrupación.

Ejemplo:

WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Almacén", "Empresa");

Este ejemplo obtiene los valores de todos los almacenes disponibles en la empresa seleccionada.

Visualización de campos de referencia

Cuando visualizas campos de referencia en un informe (así como en situaciones en las que necesitas mostrar una presentación, como mostrar un valor utilizando la función Mensaje), ten en cuenta que para mostrar un valor que es una referencia, la aplicación ejecuta una consulta adicional para obtener la presentación del campo. Esto reduce el rendimiento. Para evitar esto, te recomendamos que obtengas el campo de presentación para el campo de referencia directamente en la consulta y luego muestres el campo de presentación en el informe. Y es factible mostrar el campo de referencia en sí en los detalles de la celda, para que los usuarios puedan abrir el valor de la celda para ver el elemento.

Ejemplo:

SELECT
    CompanySalesTurnovers.Items AS Artículos, 
    CompanySalesTurnovers.SalesDocument.Counterparty AS ContraparteDocumentoVentas, 
    CompanySalesTurnovers.QuantityTurnover AS CantidadGiro, 
    CompanySalesTurnovers.SalesAmountTurnover AS MontoVentasGiro 
FROM 
    AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers 
TOTALS SUM(CantidadGiro), SUM(MontoVentasGiro) 
BY
    OVERALL, 
    Artículos, 
    ContraparteDocumentoVentas

Cuando el resultado de la consulta se muestra en un documento de hoja de cálculo, la aplicación ejecuta consultas adicionales para obtener las presentaciones de los campos Artículos y ContraparteDocumentoVentas, lo que reduce el rendimiento del informe. Para evitar obtener presentaciones durante la operación de visualización del informe, agrega campos de presentación a la consulta y pásalos al informe.

Ejemplo:

SELECT 
    CompanySalesTurnovers.Items AS Artículos, 
    CompanySalesTurnovers.Items.Presentation AS PresentaciónArtículo, 
    CompanySalesTurnovers.SalesDocument.Counterparty AS ContraparteDocumentoVentas,
    CompanySalesTurnovers.SalesDocument.Counterparty.Presentation AS PresentaciónContraparte, 
    CompanySalesTurnovers.QuantityTurnover AS CantidadGiro, 
    CompanySalesTurnovers.SalesAmountTurnover AS MontoVentasGiro
FROM 
    AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers
TOTALS SUM(CantidadGiro), SUM(MontoVentasGiro)
BY
    OVERALL, 
    Artículos, 
    ContraparteDocumentoVentas

Los campos PresentaciónArtículo y PresentaciónContraparte que se muestran en el documento de hoja de cálculo deben tomarse de esta consulta.

Ten en cuenta que el asistente de formulario de salida disponible en Designer sigue las reglas anteriores. Agrega automáticamente los campos de presentación requeridos a una consulta y se asegura de que se muestren en el informe.

También vale la pena mencionar que obtener atributos de campos de referencia utilizando . (punto) mientras se muestra un informe puede afectar significativamente el rendimiento. Por lo tanto, para mostrar los atributos de los campos de referencia, debes obtenerlos directamente en la consulta en lugar de a través de una referencia cuando se muestra el informe.

Por ejemplo, para mostrar el campo Código de un elemento, use la siguiente consulta:

SELECT 
    CompanySalesTurnovers.Items AS Items, 
    CompanySalesTurnovers.Items.Presentation AS PresentaciónDelElemento,   
    CompanySalesTurnovers.Items.Code AS CódigoDelElemento,  
    CompanySalesTurnovers.SalesDocument.Counterparty AS ContraparteDelDocumentoDeVenta,
    CompanySalesTurnovers.SalesDocument.Counterparty.Presentation AS PresentaciónDeLaContraparte, 
    CompanySalesTurnovers.QuantityTurnover AS CantidadDeRotación, 
    CompanySalesTurnovers.SalesAmountTurnover AS MontoDeVentaDeRotación
FROM 
    AccumulationRegister.CompanySales.Turnovers AS CompanySalesTurnovers
TOTALS SUM(CantidadDeRotación), SUM(MontoDeVentaDeRotación)
BY 
    OVERALL, 
    Items, 
    ContraparteDelDocumentoDeVenta

Y mostrar el campo CódigoDelElemento en el informe en lugar de obtener el atributo Código utilizando una referencia.

Complementando el resultado de la consulta por días pertenecientes a un período especificado

A veces se necesita un informe donde los totales se calculan para cada fecha en un rango de tiempo especificado con una periodicidad dada. Por ejemplo, uno podría necesitar obtener rotaciones por semanas independientemente de la presencia real de rotaciones en semanas específicas. El lenguaje de consulta tiene una palabra clave PERIODS para este propósito. Se agrega a la cláusula TOTALS, después del campo Periodo. La palabra clave PERIODS va seguida del tipo de período entre paréntesis (uno de los siguientes: Segundo, Minuto, Hora, Día, DiezDías, Semana, Mes, Trimestre, Semestre y Año), y las fechas de inicio y fin del período requerido. Si no se especifican las fechas de inicio y fin, se utilizan las primeras y últimas fechas del resultado.

Ejemplo:

SELECT 
    ItemAccountingTurnovers.Period AS Periodo,
    ItemAccountingTurnovers.QuantityTurnover AS CantidadDeRotación
FROM
    AccumulationRegister.ItemAccounting.Turnovers(, , Semana, ) AS ItemAccountingTurnovers
ORDER BY 
    Periodo 
TOTALS SUM(CantidadDeRotación) BY
    Periodo PERIODS(SEMANA,,)

El resultado de esta consulta se complementa con registros para el comienzo de cada semana.

Tenga en cuenta que dado que no existen registros detallados para los períodos complementados en el resultado de la consulta, los registros complementados solo se adquieren de la selección si todos los períodos que participan en la consulta se recuperan durante la iteración a través de la selección. Esto se hace especificando el tercer parámetro de la función Select() del resultado de la consulta.

Ejemplo:

PeriodSelection = Result.Select(QueryResultIteration.ByGroups, "Periodo", "Todos");
Mientras PeriodSelection.Siguiente() Hacer
    PeriodArea.Parameters.Fill(PeriodSelection);
    SpreadsheetDoc.Put(PeriodArea, PeriodSelection.Level());
FinHacer;

Uso de filtros en consultas con tablas virtuales

Para simplificar la codificación de consultas típicas, la plataforma proporciona un conjunto de tablas virtuales que se pueden tratar como consultas parametrizadas. El lenguaje de consulta proporciona dos métodos de definición de filtros: en la declaración de consulta WHERE y utilizando un parámetro de tabla virtual. Esta sección cubre las diferencias entre los dos métodos.

Consideremos dos consultas al mismo registro de acumulación que seleccionan el saldo actual de un producto especificado. Supongamos que el registro tiene la siguiente estructura:

Dimensión: Elemento
Recurso: Cantidad

1. Una consulta con un filtro especificado utilizando una declaración de lenguaje de consulta se ve así:

SELECT 
    SaldoDeCantidad
FROM
    AccumulationRegister.Inventario.Saldo()
WHERE
    Elemento = &Elemento

2. Una consulta con un filtro especificado utilizando un parámetro de tabla virtual se ve así:

SELECT
    SaldoDeCantidad
FROM
    AccumulationRegister.Inventario.Saldo(, Elemento = &Elemento)

Ambas consultas producen el mismo resultado. Pero cuando se ejecuta la primera consulta, se calculan los saldos para todos los productos y solo entonces se selecciona el saldo requerido. La segunda consulta transfiere la condición de filtro directamente a la tabla virtual y cuando se ejecuta la consulta a la tabla virtual, solo se adquiere el saldo para un solo producto. Por lo tanto, si se define una restricción mediante el lenguaje de consulta, primero se recupera una selección completa de una tabla virtual y solo entonces se aplica un filtro. Y si se pasa una condición de consulta a una tabla virtual, la selección resultante se limita por esa condición. Por lo tanto, recomendamos que pase tantas condiciones de filtro como sea posible en los parámetros de la tabla virtual.

Para algunas tablas virtuales, el método de filtro afecta no solo al rendimiento sino también a la lógica de ejecución de la consulta. Consideremos la tabla virtual SliceLast de un registro de información en el ejemplo del registro de información CurrencyExchangeRates, que tiene la siguiente estructura:

Dimensión: Moneda
Atributo: Fuente
Recurso: Tasa de cambio

donde el atributo Fuente puede tener los siguientes valores: “Internet” y “Medios impresos”.

El objetivo es obtener las últimas tasas de cambio de divisas con los valores recopilados de los medios impresos.

1. Una consulta con un filtro creado usando una declaración de lenguaje de consulta se ve así:

SELECT
    Moneda, TasaDeCambio
FROM
    RegistroDeInformacion.CurrencyExchangeRates.SliceLast()
WHERE
    Item = &Item

2. Una consulta con un filtro especificado usando un parámetro de tabla virtual se ve así:

SELECT
    Moneda, TasaDeCambio
FROM
    RegistroDeInformacion.CurrencyExchangeRates.SliceLast(, Fuente = &Fuente)

Aunque estas consultas solo difieren por el método utilizado para definir el filtro, sus resultados son diferentes (a diferencia de los resultados de las consultas al registro de acumulación). El resultado de la primera consulta no incluye las monedas que no tienen sus últimas tasas de cambio establecidas por los medios impresos.

En la segunda consulta se seleccionan las últimas tasas de cambio para todas las monedas que han tenido sus tasas de cambio establecidas por los medios impresos al menos una vez. Entonces, si un registro almacena los siguientes datos:

Período	Moneda	Tasa de cambio	Fuente
19/03/2014	USD	31.00	medios impresos
19/03/2014	EUR	34.00	medios impresos
20/03/2014	USD	31.10	Internet

La primera consulta devuelve:

Moneda	Tasa de cambio
EUR	34.00

La segunda consulta devuelve:

Moneda	Tipo de cambio
USD	31.00
EUR	34.00

Uso de la cláusula FOR UPDATE en el lenguaje de consulta

La cláusula FOR UPDATE está destinada a bloquear datos específicos (que están disponibles para la lectura desde una transacción perteneciente a otra conexión) de antemano mientras se están leyendo, para evitar bloqueos más adelante, cuando se vayan a escribir. Puede especificar las tablas cuyos datos se modificarán después de la lectura. En este escenario, otra conexión esperará a que los datos se liberen desde el momento en que se inicie la lectura de datos dentro de una transacción, es decir, no podrá leer datos bloqueados hasta que se complete la transacción de bloqueo.
Tenga en cuenta que el bloqueo que prohíbe la modificación de los datos que se están leyendo en una transacción se establece de forma independiente de la cláusula FOR UPDATE. Esto significa que si se están leyendo algunos datos dentro de una transacción, estos datos no se pueden modificar antes de que se libere el bloqueo. Si se ejecuta una consulta fuera de una transacción, puede leer datos bloqueados.

Los bloqueos se establecen durante la ejecución de la consulta y se liberan cuando se completa la transacción. Si se ejecuta una consulta fuera de una transacción, se ignora la cláusula FOR UPDATE.

Si la cláusula FOR UPDATE no va seguida de ningún nombre de tabla, se bloquean los datos leídos de todas las tablas involucradas en la consulta. Si se especifican tablas, solo se bloquean los datos de estas tablas. Para fines de bloqueo, solo puede especificar tablas de nivel superior (es decir, no las secciones tabulares) que estén involucradas en la consulta. Especifique los nombres de las tablas en lugar de sus alias definidos en la consulta. Si especifica una tabla virtual, se bloquean los datos de todas las tablas que forman la tabla virtual. Especifique el nombre de la tabla virtual sin ningún parámetro.

En este ejemplo de uso de la cláusula FOR UPDATE, una consulta bloquea el registro de saldo:

SELECT 
   Doc.Date, 
   Doc.Ref, 
   Doc.Company, 
   Doc.Counterparty, 
   Doc.MutualSettlementAgreement,
   Doc.DocumentTotal, 
   Doc.MutualSettlementAgreement.MonitorTotalDebt 
       AS MonitorTotalDebt,
   Doc.MutualSettlementAgreement.MaximumTotalDebt
       AS MaximumTotalDebt,
   MutualSettlementsByAgreement.AmountBalance AS AmountBalanceByAgreement
FROM 
   Document.RetailSale Doc

LEFT JOIN // Monitoreo del monto de la deuda para el acuerdo
   AccumulationRegister.CounterpartiesMutualSettlementsCompanies.Balance(, 
             MutualSettlementAgreement = &MutualSettlementAgreement) 
      AS MutualSettlementsByAgreement
   ON True

WHERE 
   Doc.Ref = &DocumentRef
FOR UPDATE AccumulationRegister.MutualSettlementsByAgreement.Balance 
                           // Mientras se lee la tabla de saldos del registro,
                           // no se puede escribir datos en esta tabla
                           // para evitar colisiones

Uso de expresiones personalizadas en totales del lenguaje de consulta

El lenguaje de consulta permite el uso de expresiones personalizadas para calcular totales por grupos. Este artículo cubre algunas especificidades del uso de expresiones personalizadas en campos de totales y proporciona ejemplos de uso.

Expresiones personalizadas en totales

La sintaxis y la funcionalidad de una expresión para calcular totales son similares a una expresión regular del lenguaje de consulta, aunque hay algunas diferencias:

en las expresiones de cálculo de totales no se pueden utilizar las operaciones IN y IN HIERARCHY con consultas anidadas;
en las expresiones de cálculo de totales se puede utilizar el resultado de la función Presentation() en otra expresión.

Si se utiliza una expresión personalizada para calcular totales, debe especificar el nombre del campo que almacenará el resultado del cálculo. Para hacer esto, agregue un alias de campo de la lista de selección de la consulta después de la descripción de la expresión total (después de la palabra clave opcional AS).

Ejemplo:

TOTALES
    100 * Sum(Ganancia) / SUM(Ventas) AS PorcentajeGanancia

En este ejemplo, el resultado de la expresión se almacena en el campo PorcentajeGanancia. Tenga en cuenta que el campo PorcentajeGanancia debe estar presente en la lista de selección de la consulta.

Todos los campos utilizados en las expresiones de cálculo de totales deben estar presentes en la lista de selección de la consulta. Para los nombres de los campos, puede utilizar los alias de los campos de la lista de selección.

Solo se pueden utilizar aquellos campos por los cuales se calculan los totales en las expresiones para calcular totales fuera de las funciones de agregado.

Ejemplo:

TOTALES
    Artículos AS Artículos, // Esto es correcto porque el campo está presente en la lista de agrupaciones totales
    MontoVentas AS MontoVentas, // Esto es incorrecto porque el campo está ausente de la lista de agrupaciones totales
    SUM(CantidadVentas) AS CantidadVentas // Esto es correcto porque el campo se utiliza en una función de agregado
POR
    Artículos,
    Contraparte

Si se utiliza un campo de agrupación en una expresión total y este campo no se ha utilizado para obtener una agrupación, el valor del campo es NULL.

Ejemplos de uso

Cálculo de expresiones basadas en funciones de agregado

Para calcular expresiones basadas en funciones de agregado, es suficiente proporcionar una expresión que utilice funciones de agregado.

Ejemplo:

SELECCIONAR
    Artículos,
    MontoVentas AS Ventas,
    MontoGanancia AS Ganancia,
    100 * MontoGanancia / MontoVentas AS PorcentajeGanancia
DE
    RegistroAcumulativo.CuentaGanancias.Ventas
TOTALES
    SUM(Ventas),
    SUM(Ganancia),
    100 * SUM(Ganancia) / SUM(Ventas) AS PorcentajeGanancia
POR
    Artículos JERARQUÍA

En este ejemplo, el campo PorcentajeGanancia de los registros totales almacenará el valor de la suma por el campo Ganancia multiplicado por 100 y dividido por el valor de la suma por el campo Ventas.

Cálculo de totales solo para agrupaciones recuperadas

Para calcular los valores de algunos campos totales solo para las agrupaciones cuyos totales ya se han recuperado, puede utilizar el hecho de que los valores de los campos de agrupación son NULL para las agrupaciones cuyos totales aún no se han recuperado.

Ejemplo:

SELECCIONAR
    SaldoPrimario.Cuenta AS Cuenta,
    SaldoPrimario.Moneda AS Moneda,
    SaldoPrimario.MontoSaldo AS MontoSaldo,
    SaldoPrimario.MontoMonedaSaldo AS MontoMonedaSaldo
DE
    RegistroContable.Primario.Saldo AS SaldoPrimario
TOTALES
    SUM(MontoSaldo),
    CASE
        CUANDO Moneda NO ES NULL
            ENTONCES SUM(MontoMonedaSaldo)
    FIN AS MontoMonedaSaldo
POR
    Cuenta,
    Moneda

En este ejemplo, los valores totales para el campo MontoMonedaSaldo solo se calculan para los totales que tienen la agrupación Moneda recuperada. Por lo tanto, los totales para la agrupación Cuenta por el campo MontoMonedaSaldo solo se calculan cuando los totales de agrupación se recuperan solo dentro de la agrupación Moneda.
Otra forma de calcular totales es verificar el número de valores diferentes en una agrupación y calcular los totales solo para los registros totales que se calculan para un único valor de agrupación.

Ejemplo:

SELECCIONAR
    SaldoPrimario.Cuenta AS Cuenta,
    SaldoPrimario.Moneda AS Moneda,
    SaldoPrimario.MontoSaldo AS MontoSaldo,
    SaldoPrimario.SumaMonedaSaldo AS SumaMonedaSaldo
DE
    RegistroContable.Primario.Saldo AS SaldoPrimario
TOTALES
    SUM(MontoSaldo),
    CASE
        CUANDO COUNT(DISTINCT Moneda) = 1
            ENTONCES SUM(MontoMonedaSaldo)
    FIN AS MontoMonedaSaldo
POR
    Cuenta,
    Moneda

En este ejemplo, el cálculo de totales para el campo MontoMonedaSaldo se realiza para aquellos registros totales que incluyen un único valor del campo Moneda.

Redefinir presentaciones de puntos de agrupación

Si un campo es una presentación de un campo de agrupación, redefinir la expresión para este campo conduce a mostrar el resultado de la expresión total como una presentación de campo. Esta característica le permite redefinir presentaciones para agrupaciones.

Ejemplo:

SELECCIONAR
    VentasRotaciones.Artículos AS Artículos,
    VentasRotaciones.Artículos.Código AS CódigoArtículo,
    PRESENTACIÓN(VentasRotaciones.Artículos) AS PresentaciónArtículo,
    VentasRotaciones.CantidadRotación AS CantidadRotación,
    VentasRotaciones.MontoRotación AS MontoRotación
DESDE
    RegistroAcumulación.Ventas.Rotaciones AS VentasRotaciones
TOTALES
    CódigoArtículo + " " + PresentaciónArtículo AS PresentaciónArtículo,
    SUM(CantidadRotación),
    SUM(MontoRotación)
POR
    Artículos

En este ejemplo, la presentación del campo Artículos en los registros totales es una expresión que contiene un código y la presentación predeterminada.

Ordenamiento en consultas anidadas

El lenguaje de consulta admite el ordenamiento en consultas anidadas. Puede utilizar el ordenamiento en una consulta anidada si la consulta tiene una limitación en el número de registros recuperados. Esto significa que una consulta anidada solo puede incluir ordenamiento si incluye la instrucción TOP.

Por ejemplo, consideremos una consulta que obtiene los volúmenes de ventas de los cinco productos más caros.

SELECCIONAR
    ConsultaAnidada.Ref,
    VentasRotaciones.CantidadRotación,
    VentasRotaciones.MontoRotación
DESDE
    (SELECCIONAR TOP 5
        Artículos.Ref AS Ref
    DESDE
        Catálogo.Artículos AS Artículos

    ORDENAR POR
        Artículos.PrecioCompra DESC) AS ConsultaAnidada
        IZQUIERDA UNIR RegistroAcumulación.Ventas.Rotaciones(,,,
            ARTÍCULO EN
                (SELECCIONAR TOP 5
                    Artículos.Ref AS Ref
                DESDE
                    Catálogo.Artículos AS Artículos
                ORDENAR POR
                    Artículos.PrecioCompra DESC)) AS VentasRotaciones
        POR ConsultaAnidada.Ref = VentasRotaciones.Artículos

En esta consulta, la consulta anidada obtiene los cinco productos con el valor más alto en el campo PrecioCompra, y en la consulta principal los productos recuperados se vinculan al registro de ventas que proporciona los datos de ventas. Para limitar el cálculo de las rotaciones, la lista de los productos más caros también se pasa a la condición de la tabla RegistroAcumulación.Ventas.Rotaciones.

Uso de la función ISNULL()

Cuando trabaja con el lenguaje de consulta, a veces es necesario reemplazar el valor NULL por otro valor. Es posible que necesite esto para obtener los saldos de almacén de todos los productos. Este artículo cubre el uso de la función ISNULL() en el lenguaje de consulta en tales escenarios y también considera otras soluciones posibles.

La función ISNULL

El lenguaje de consulta incluye la función ISNULL(), que está destinada a reemplazar una expresión por otra si su valor es NULL. Utiliza la siguiente sintaxis:

ISNULL(<Expresión que se verifica>, <Expresión de sustitución>)

Esta función devuelve el valor del primer parámetro si no es NULL, de lo contrario devuelve el valor del segundo parámetro.

Ejemplo:

SELECCIONAR
    DescripciónArtículos,
    ISNULL(SaldoContableArtículo.CantidadSaldo, 0) AS SaldoCantidad
DESDE
    Catálogo.Artículos AS ArtículosCatalogo
        IZQUIERDA UNIR RegistroAcumulación.SaldoContableArtículo AS SaldoContableArtículo
        EN SaldoContableArtículo.Artículos = ArtículosCatalogo.Ref
DONDE
    ArtículosCatalogo.EsCarpeta = FALSO

Este código obtiene todos los artículos del catálogo de productos y luego obtiene los saldos actuales para cada producto en el registro de acumulación. Dado que la tabla virtual no devuelve registros para los productos que no tienen saldos, como resultado de la unión, el campo SaldoContableArtículo.CantidadSaldo almacenará los valores NULL para los productos sin saldos. Para tener 0 en lugar de NULL en el resultado de la consulta, estamos utilizando la función ISNULL() que realiza la sustitución requerida.

Uso de la operación CASE

Otro método para tratar la situación descrita es utilizar la operación CASE del lenguaje de consulta. Una consulta similar a la anterior se verá así:

SELECCIONAR
    DescripciónArtículos,
    CASE CUANDO SaldoContableArtículo.CantidadSaldo ES NULL ENTONCES 0 ELSE SaldoContableArtículo.CantidadSaldo AS SaldoCantidad
DESDE
    Catálogo.Artículos AS ArtículosCatalogo
        IZQUIERDA UNIR RegistroAcumulación.SaldoContableArtículo AS SaldoContableArtículo
        EN SaldoContableArtículo.Artículos = ArtículosCatalogo.Ref
DONDE
    ArtículosCatalogo.EsCarpeta = FALSO

El resultado de esta consulta es idéntico al de la consulta de la sección anterior.

Usar la función ISNULL() es preferible a usar la operación CASE por las siguientes razones: el código con ISNULL() es más corto, lo que significa una mejor legibilidad. Además, cuando la expresión que se está verificando es una función compleja (función de agregado, entre otras), la expresión ISNULL() se puede calcular más rápido que la expresión CASE.

Especificidades de la función ISNULL

Aunque ISNULL() es equivalente a la operación CASE que verifica si un valor es NULL, todavía hay una diferencia: cuando la expresión es una cadena o un número, la expresión de sustitución se convierte a ese tipo.

Por ejemplo, cuando la expresión que se está verificando tiene tipo String(5) y la expresión de sustitución tiene tipo String(10), el resultado se convierte a tipo String(5). Por lo tanto, cuando la función devuelve la expresión de sustitución, su valor se corta a cinco caracteres.

Es similar para expresiones numéricas: el valor se convierte al tipo de la expresión que se está verificando, es decir, el valor de sustitución puede ser cortado. Si un valor no se puede convertir, el lenguaje de consulta aborta la ejecución de la consulta con un error. Por ejemplo, convertir el número 1000 a tipo Number(2) resulta en un error.

Detalles sobre cómo usar el operador de inclusión de grupo

El lenguaje de consulta de 1C:Enterprise incluye una forma generalizada del operador IN/ NOT IN. Esta sección describe cómo se ejecuta este operador. Este conocimiento ayuda con la optimización de consultas.

Operador de inclusión de grupo

El operador de inclusión tiene la siguiente sintaxis:

<Expresión> IN (<Lista de valores>)

El operador de inclusión es uno de los operadores de comparación definidos en el lenguaje de consulta de 1C:Enterprise. Su valor es Verdadero si el valor del operando izquierdo está en la lista de valores del operando derecho. La lista de valores se puede definir como:

una lista de expresiones;
un parámetro con un valor que es una colección de valores;
una consulta anidada.

Si una lista de valores está definida por una consulta anidada, se puede usar la forma generalizada del operador de inclusión, es decir, el operador de inclusión de grupo:

(<Expresión 1>, ..., <Expresión N>) IN (<Consulta anidada>)

Una consulta anidada debe definir una selección de N columnas.

El valor del operador de inclusión de grupo es Verdadero si el registro (<Expresión 1>, …, <Expresión N>) coincide con uno de los registros en la selección definida por la consulta anidada. Por ejemplo, supongamos que el operando izquierdo es una lista:

(1, 2, 3).

Si el resultado de la consulta anidada es la siguiente selección:

1, 1, 1
2, 2, 2
2, 2, 3

el resultado de este operador de inclusión de grupo es Falso. Si el resultado de la consulta anidada es otra selección:

1, 1, 1
1, 2, 3
2, 2, 3

el resultado del operador de inclusión de grupo es Verdadero.

Además del operador de inclusión de grupo (IN), el lenguaje de consulta también proporciona un operador de no inclusión (NOT IN). El resultado de este operador es una negación del operador IN con los mismos operandos. La explicación anterior sobre el operador IN también es válida para NOT IN.

Implementación del operador de inclusión de grupo

A diferencia de un operador de inclusión simple que tiene algunos análogos en la mayoría de los sistemas de gestión de bases de datos (el operador IN del lenguaje SQL), el operador de inclusión de grupo no tiene un equivalente exacto en SQL. Por lo tanto, cuando se utiliza el operador de inclusión de grupo, se debe tener en cuenta la forma en que se traduce a SQL. El siguiente operador:

(<Expresión 1>, ..., <Expresión N>) IN (
    SELECT <Columna 1>, ..., <Columna N>
    FROM <Fuentes>
    WHERE <Condición>
)

se ejecuta de la siguiente manera en el DBMS:

EXISTS(
    SELECT 1
    FROM <Fuentes>
    WHERE (<Condición>) AND <Expresión 1> = <Columna 1> AND ... AND <Expresión N> = <Columna N>
)

Si una consulta anidada contiene funciones de agregado y/o una sección GROUP BY, el siguiente operador de inclusión de grupo:

(<Expresión 1>, ..., <Expresión N>) EN (
    SELECCIONAR <Columna 1>, ..., <Columna N>
    DESDE <Fuentes>
    DONDE <Condición 1>
    AGRUPAR POR <Lista de agrupación>
    TENIENDO <Condición 2>
)

se codifica de la siguiente manera en SQL:

EXISTS(
    SELECCIONAR 1
    DESDE <Fuentes>
    DONDE <Condición 1>
    AGRUPAR POR <Lista de agrupación>
    TENIENDO (<Condición 2>) Y <Expresión 1> = <Columna 1> Y ... Y <Expresión N> = <Columna N>
)

Notas sobre la velocidad de ejecución

La velocidad de ejecución de una consulta que contiene el operador de inclusión de grupo depende de la capacidad del optimizador del plan de consulta del sistema de gestión de bases de datos para implementar eficientemente el operador EXISTS con una consulta anidada. Por lo tanto, es difícil predecir si una consulta específica se ejecutará eficientemente en general. Sin embargo, es posible ofrecer algunas recomendaciones que pueden ayudar a generar consultas eficientes.

Tablas cortas

El uso de un operador de inclusión de grupo en una consulta no disminuye el rendimiento si la consulta anidada genera una selección de tablas con un pequeño número de registros, y estos números no pueden aumentar significativamente durante la operación del sistema con el crecimiento de la base de datos acumulada.

Índices

Debe recordar que una consulta anidada en un operador EXISTS se ejecuta varias veces al verificar la condición de filtro para cada registro. Por lo tanto, si las tablas utilizadas en una consulta anidada pueden incluir un gran número de registros, cualquier búsqueda dentro de las tablas debe ser optimizada mediante la definición de los índices apropiados:

para los valores de las columnas <Columna 1>, …, <Columna N> solo use nombres de campos de tabla (no expresiones);
seleccione un campo de aquellos utilizados como valores de las columnas <Columna 1>, …, <Columna N> con sus valores repetidos con menos frecuencia (el campo más selectivo) y habilite la indexación por este campo;
no utilice funciones de agregación y agrupaciones en una consulta anidada en un operador de inclusión de grupo;
incluya los campos más selectivos en la lista del operador de inclusión de grupo. Por ejemplo, cuando verifica los saldos de almacén por artículos de productos de la sección tabular del documento PurchaseOrder, al que se hace referencia mediante el parámetro DocumentRef, se ejecutará la siguiente consulta lentamente porque es imposible generar un índice a partir de un grupo de campos (Items, ItemProperty, Quality) en la tabla Document.PurchaseOrder.Goods.

SELECCIONAR
    Doc.Items.Presentation AS ItemsPresentation,
    Doc.Items.UnitOfMeasurement.Presentation AS UnitOfMeasurementPresentation,
    Doc.ItemProperty AS ItemProperty,
    Doc.ItemSeries AS ItemSeries,
    Doc.Quality AS Quality,
    SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3))) AS DocumentQuantity,
    ISNULL(MAX(Balance.QuantityBalance), 0) AS BalanceQuantity
DESDE 
    Document.SalesInvoice.Goods AS Doc
    IZQUIERDA UNIR
        AccumulationRegister.GoodsToBeShippedFromWarehouses.Balance(,
            Warehouse = &Warehouse Y (Items, ItemProperty, Quality) EN (
                SELECCIONAR
                    Doc.Items, Doc.ItemProperty, Doc.Quality
                DESDE
                    Document.SalesInvoice.Goods AS Doc
                DONDE
                    Doc.Ref = &DocumentRef
                    Y NO Doc.Items.Package 
            ) Y &ShippingDocument = ShippingDocument) AS Balance
    EN 
        Doc.Items = Balance.Items
        Y Doc.ItemProperty = Balance.ItemProperty
        Y Doc.Quality = Balance.Quality
        Y ((NO Doc.SeriesSpecifiedWhenShipping Y Balance.ItemSeries = Doc.ItemSeries) O
            (Doc.SeriesSpecifiedWhenShipping Y Balance.ItemSeries = &EmptySeries))
DONDE
    Doc.Ref = &DocumentRef
AGRUPAR POR
    Doc.Items,
    Doc.ItemProperty,
    Doc.ItemSeries,
    Doc.Quality,
    &ShippingDocument
TENIENDO
    ISNULL(MAX(Balance.QuantityBalance), 0) < SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3)))

La siguiente consulta es diferente a la anterior en que el operador de inclusión de grupo tiene el campo Warehouse agregado, que se utiliza para la indexación en la tabla Document.PurchaseOrder.Goods. Esto resulta en una reducción significativa del tiempo de ejecución de la consulta.

SELECT
    Doc.Items.Presentation AS PresentaciónDeArtículos,
    Doc.Items.UnitOfMeasurement.Presentation AS PresentaciónDeUnidadDeMedida,
    Doc.ItemProperty AS PropiedadDeArtículo,
    Doc.ItemSeries AS SerieDeArtículo,
    Doc.Quality AS Calidad,
    SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3))) AS CantidadDeDocumento,
    ISNULL(MAX(Balance.QuantityBalance), 0) AS CantidadDeBalance
FROM 
    Document.SalesInvoice.Goods AS Doc
    LEFT JOIN
        AccumulationRegister.GoodsToBeShippedFromWarehouses.Balance(,
            (Warehouse, Items, Quality, ItemProperty) IN (
                SELECT
                    Doc.Ref.Warehouse, Doc.Items, Doc.Quality, Doc.ItemProperty
                FROM
                    Document.SalesInvoice.Goods AS Doc
                WHERE
                    Doc.Ref = &DocumentRef
                    AND NOT Doc.Items.Package  
            ) AND &ShippingDocument = ShippingDocument) AS Balance
     ON 
        Doc.Ref.Warehouse = Balance.Warehouse
        AND Doc.Items = Balance.Items
        AND Doc.Quality = Balance.Quality
        AND Doc.ItemProperty = Balance.ItemProperty
        AND ((NOT Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = Doc.ItemSeries) OR
            (Doc.SeriesSpecifiedWhenShipping AND Balance.ItemSeries = &EmptySeries))

WHERE
    Doc.Ref = &DocumentRef
GROUP BY
    Doc.Items,
    Doc.ItemProperty,
    Doc.ItemSeries,
    Doc.Quality,
    &ShippingDocument
HAVING
    ISNULL(MAX(Balance.QuantityBalance), 0) < SUM(CAST(Doc.Count * Doc.Rate / Doc.Items.UnitOfMeasurement.Rate AS NUMBER(15,3)))

Tablas temporales

Si una consulta anidada en el operador de inclusión de grupo es compleja, contiene funciones de agregado y utiliza tablas con un gran número de registros, la ejecución del operador de inclusión de grupo puede ser extremadamente ineficiente. En este caso, recomendamos lo siguiente:

ejecutar la consulta anidada por separado y almacenar su resultado en una tabla temporal;
definir un índice en esta tabla temporal utilizando todos los campos o el subconjunto de campos más selectivo;
utilizar la tabla temporal resultante en la consulta anidada en el operador de inclusión de grupo.

Especificaciones del uso de la cláusula GROUP BY

El lenguaje de consulta de 1C:Enterprise ofrece la cláusula GROUP BY para agrupar (colapsar) registros por campos específicos. Esta cláusula es similar a la cláusula GROUP BY del estándar SQL, pero tiene algunas peculiaridades.

La principal peculiaridad es que cuando la cláusula GROUP BY contiene un campo, todos los campos recibidos de él utilizando . (punto) también se consideran agrupados. Ejemplo:

SELECT 
 CustomerOrder.Counterparty, 
 CustomerOrder.Counterparty.Code, 
 CustomerOrder.Counterparty.Description, 
 SUM(CustomerOrder.DocumentTotal) AS DocumentTotal 
FROM 
 Document.CustomerOrder AS CustomerOrder
GROUP BY 
 CustomerOrder.Counterparty

En este ejemplo, se realizará una agrupación automática por los campos CustomerOrder.Counterparty.Code y CustomerOrder.Counterparty.Description porque la cláusula GROUP BY contiene el campo CustomerOrder.Counterparty con sus datos recibidos utilizando . (punto).

Especificaciones del trabajo con la tabla virtual de datos de programación del registro de cálculo

Se define una tabla virtual CalculationRegister.<RegisterName>.ScheduleData para los registros de cálculo que admiten un período de acción. Un registro de cálculo de este tipo debe estar vinculado a un registro de información no periódico que proporcionará datos de programación.

Para simplificar la obtención de datos de programación, el sistema tiene una tabla virtual definida que incluye los siguientes campos virtuales además de otros campos:

<Nombre del recurso de programación>BasePeriod
<Nombre del recurso de programación>ActionPeriod
<Nombre del recurso de programación>RegistrationPeriod
<Nombre del recurso de programación>ActualActionPeriod

Esto significa que para cada recurso numérico del registro de información asignado como programación para un registro de cálculo, se puede obtener su suma para las filas del registro, teniendo en cuenta el período base de la fila del registro de cálculo, el período de acción, el período de registro y el período de acción real.

Al generar una tabla virtual de datos de horarios, se unen las tablas del registro de cálculo y del registro de información, y cuando se recupera el campo <Nombre del recurso de horario>PeriodoDeAccionReal, la tercera tabla unida es la tabla del período de acción real del registro de cálculo. Dado que los datos para los cuatro campos mencionados se reciben mediante la unión con la tabla del registro de información basada en diversas condiciones, esto significa que se ejecutan hasta cuatro uniones con la tabla del registro de información.

Al obtener tablas virtuales, el sistema intenta actuar de manera sensata, es decir, solo realiza tantas uniones como sean necesarias para obtener los campos de la tabla virtual enumerados en la sección “SELECT”.

Esto garantiza que al escribir código de consulta, no es necesario seleccionar campos de tabla virtual “por si acaso”. Esta recomendación puede sonar demasiado general, pero recuerde que obtener un campo innecesario en una consulta a tablas reales u otras tablas virtuales generalmente aumentará el tráfico de datos de red y resultará en una ligera disminución del rendimiento. Es obvio que obtener cinco campos de registro apenas difiere de obtener otro, sexto campo a menos que nos refiramos a campos grandes. Pero cuando selecciona de una tabla virtual de datos de horarios, debe tener en cuenta que agregar un campo virtual más de la lista cambia la situación drásticamente. Los tiempos de ejecución de la consulta pueden variar de manera cardinal. Por lo tanto, la recomendación de codificar consultas de manera óptima con una selección cuidadosa del número de campos seleccionados es especialmente válida para esta tabla virtual.

También tenga en cuenta que el rendimiento de una consulta a una tabla virtual se ve afectado significativamente por el número de tipos de campos virtuales en lugar del número de campos.

Consideremos un ejemplo. El registro de información asignado como horario tiene los recursos NumeroDeDias y NumeroDeHoras. Revisemos las siguientes tres consultas.

SELECT 
    PeriodoDeRegistroNumeroDeDias
FROM 
    RegistroDeCalculo.Nomina.DatosDeHorario(Empresa = &Empresa)

SELECT 
    PeriodoDeRegistroNumeroDeDias, 
    PeriodoDeRegistroNumeroDeHoras
FROM 
    RegistroDeCalculo.Nomina.DatosDeHorario(Empresa = &Empresa)

SELECT 
    PeriodoDeRegistroNumeroDeDias, 
    PeriodoDeAccionNumeroDeHoras
FROM 
    RegistroDeCalculo.Nomina.DatosDeHorario(Empresa = &Empresa)

Cuando compares los tiempos de ejecución, notarás que el tiempo de ejecución de la segunda consulta es casi el mismo que el de la primera consulta, a pesar de que se agrega un nuevo campo virtual. Pero la ejecución de la tercera consulta lleva notablemente más tiempo (posiblemente varias veces más) que la segunda, aunque el número de campos seleccionados es el mismo.

Esto se debe a que para obtener los campos PeriodoDeRegistroNumeroDeDias y PeriodoDeRegistroNumeroDeHoras, se ejecuta una sola unión al registro de información por el valor en el campo PeriodoDeRegistro (independientemente del número de recursos de registro que se sumen). Y cuando también se obtiene el campo PeriodoDeAccionNumeroDeHoras, se establece otra unión al registro de información por el valor de PeriodoDeAccion.

Especificos del trabajo con el campo Presentación y la función Presentación() del lenguaje de consulta

Para mostrar campos de referencia en un informe, es necesario obtener la presentación del campo de referencia en una consulta y utilizar esta presentación en lugar de la referencia para mostrar el campo. Esta sección describe algunos aspectos específicos del campo Presentación y la función Presentación() que obtienen una presentación. Para obtener más detalles sobre la visualización de campos de referencia, consulte Visualización de campos de referencia.

Campo Presentación

Cada tabla de objeto en la base de datos tiene un campo virtual Presentación. Este campo contiene la presentación del objeto de texto. Puede recuperar este campo en una consulta al igual que otros campos de tabla, pero no puede realizar ninguna operación con este campo. Esto se debe a que este es un campo virtual y cuando obtiene este campo de la base de datos, la consulta realmente obtiene múltiples campos y convierte los valores recibidos en una cadena después de que se genera el resultado de la consulta. Por lo tanto, lo único que puede hacer con el campo Presentación es obtenerlo como parte de un resultado de consulta.

Por lo tanto, recomendamos que no ordene el resultado de la consulta por el campo Presentation porque tendrá un efecto inesperado: el resultado de la consulta se ordenará en el orden de las referencias de objeto ascendentes. Para más detalles, consulte Especificaciones de ordenación por campos de referencia.

Función Presentation()

La función Presentation se utiliza para obtener una presentación de texto de cualquier valor que se pueda obtener utilizando el lenguaje de consulta. La función Presentation() se puede utilizar tanto para tipos de referencia como para tipos primitivos. Para tipos de referencia, el resultado de la función es idéntico a obtener el campo Presentation de una referencia pasada como parámetro de función. Para tipos primitivos, la función devuelve una cadena que es el resultado de la conversión del valor que se ha pasado como parámetro. Una característica distintiva de esta función es que su resultado no se puede utilizar en ninguna expresión. Esta característica se debe al hecho de que la conversión de valores en una cadena se realiza cuando se reciben los datos del resultado de la consulta, ya que la conversión de un valor arbitrario a una cadena al ejecutar la consulta en el lado del servidor no se realiza porque se deben tener en cuenta la configuración local al convertir valores en una cadena.

El uso de la función Presentation() tiene varias ventajas en comparación con el uso del campo Presentation. Por ejemplo, si el campo del que se recibe una presentación puede contener tanto tipos de referencia como tipos primitivos, obtener el campo Presentation de este campo utilizando . (punto) no devuelve presentaciones para los valores primitivos. Pero si aplica la función Presentation() a este campo, se genera una presentación de cadena independientemente del tipo de valor en el campo. Además, si se aplica la función Presentation() a un campo que es una referencia a más de tres tablas, el lenguaje de consulta solo obtiene valores de referencia de la base de datos mientras que los valores de presentación se obtienen a través de una o más consultas adicionales. Este comportamiento hace que la obtención de presentaciones para los campos que hacen referencia a un gran número de tablas (por ejemplo, campos que hacen referencia a cualquier catálogo) sea más eficiente debido al hecho de que la consulta ejecutada no incluye un gran número de conexiones requeridas para obtener los campos de presentación.

La función Presentation() también puede ser útil cuando se obtiene la presentación de un campo de enumeración y la consulta se ejecuta a través de la conexión COM.

Especificaciones de conexión a la tabla de saldos virtuales

Introducción

Los desarrolladores de configuraciones que automatizan actividades minoristas y de comercialización a menudo necesitan algoritmos que verifiquen la disponibilidad de una cantidad de producto requerida en un almacén antes de procesar los documentos. Esto se maneja de manera eficiente con el lenguaje de consulta conectando una sección tabular de un documento con una tabla de saldos para verificar los saldos. Esta sección cubre algunos problemas que los desarrolladores pueden encontrar durante la implementación de esta función.

Verificación de saldos

Una consulta para obtener los saldos de los productos disponibles en una sección tabular tiene el siguiente formato:

SELECT
    SalesOrderContent.Items,
    ItemAccountingBalance.QuantityBalance
FROM
    Document.SalesOrder.Content AS SalesOrderContent
        LEFT JOIN AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance
        ON ItemAccountingBalance.Items = SalesOrderContent.Items
WHERE
    SalesOrderContent.Ref = &Document

Asegúrese de prestar atención a los siguientes factores. En primer lugar, cuando un producto no está disponible en el almacén, el campo ItemAccountingBalance.QuantityBalance tiene el valor NULL, por lo que cualquier comparación con él es inválida y devuelve un resultado que se trata como el valor FALSE. Tenga esto en cuenta al crear una consulta. Por ejemplo, para obtener productos cuyas cantidades en el almacén podrían ser inferiores a las cantidades en un documento, utilice el siguiente formato de consulta:

SELECCIONAR
    SalesOrderContent.Items,
    ItemAccounting.Balance.QuantityBalance
DESDE
    Document.SalesOrder.Content COMO SalesOrderContent
        UNIRSE AccumulationRegister.ItemAccounting.Balance COMO ItemAccountingBalance
        EN ItemAccounting.Balance.Items = SalesOrderContent.Items
DONDE
    SalesOrderContent.Ref = &Document Y
    (ItemAccounting.Balance.QuantityBalance < SalesOrderContent.Quantity O
        ItemAccounting.Balance.QuantityBalance ES NULO)

Esta consulta verifica que la cantidad en el almacén de un producto sea menor que la cantidad requerida o que el producto no esté disponible en absoluto.

El siguiente factor importante es limitar el número de cálculos de saldo. En este ejemplo, la tabla virtual de saldos primero calcula los saldos para todos los productos y luego conecta los productos encontrados en la sección tabular a la sección tabular. Para asegurar el cálculo de saldos solo para aquellos productos que están en la orden de venta, puede pasar la limitación a la tabla de saldos (consulte Uso de filtros en consultas con tablas virtuales). La consulta corregida es la siguiente:

SELECCIONAR
    SalesOrderContent.Items,
    ItemAccountingBalance.QuantityBalance
DESDE
    Document.SalesOrder.Content COMO SalesOrderContent
        UNIRSE AccumulationRegister.ItemAccounting.Balance(,
            Item EN (
                SELECCIONAR Items
                DESDE Document.SalesOrder.Content
                DONDE Ref = &Document)) COMO ItemAccountingBalance
        EN ItemAcountingBalance.Items = SalesOrderContent.Items
DONDE
    ItemAccountingBalance.Ref = &Document Y
    (ItemAccountingBalance.QuantityBalance < SalesOrderContent.Quantity O
        ItemAccountingBalance.QuantityBalance ES NULO)

Otra cosa importante es que un documento puede tener varias filas con el mismo producto. Para verificar los saldos correctamente, la consulta debe modificarse para que la cantidad total de un producto en el documento se compare con los saldos. La consulta modificada es la siguiente:

SELECCIONAR
    Content.Items,
    Content.Quantity,
    ItemAccountingBalances.QuantityBalance
DESDE
    (SELECCIONAR 
         SalesOrderContent.Items AS Items,
         SUM(SalesOrderContent.Quantity) AS Quantity
     DESDE
         Document.SalesOrder.Content COMO SalesOrderContent
     DONDE SalesOrderContent.Ref = &Document
     AGRUPAR POR SalesOrderContent.Items) COMO Content
         UNIRSE AccumulationRegister.ItemAccounting.Balance(,
             Items EN (
                 SELECCIONAR 
                     Document.SalesOrder.Content.Items 
                 DESDE
                     Document.SalesOrder.Content 
                 DONDE
                     Document.SalesOrder.Content.Ref = &Document)
             ) COMO ItemAccountingBalances 
         EN ItemAccountingBalances.Items = Content.Items
DONDE
    (ItemAccountingBalances.QuantityBalance < Content.Quantity 
    O (ItemAccountingBalances.QuantityBalance) ES NULO )

Como se puede ver en la consulta, comenzamos calculando el número total de cada producto en el documento y solo luego ejecutamos todas las operaciones que involucran la comparación de saldos.

Lo último de lo que hay que tener en cuenta es mostrar información sobre la cantidad insuficiente de productos a un usuario. Como hemos descrito en detalle en Mostrar campos de referencia, para mostrar información de productos a un usuario, recomendamos obtener la presentación del producto utilizando una consulta y mostrar esta presentación al usuario. Aquí hay una consulta modificada que también obtiene presentaciones de productos:

SELECCIONAR
    Content.Items, 
    Content.Items.Presentation AS Presentation, 
    Content.Quantity,
    ItemAccountingBalances.QuantityBalance
DESDE
    (SELECCIONAR 
         SalesOrderContent.Items AS Items,
         SUM(SalesOrderContent.Quantity) AS Quantity
     DESDE
         Document.SalesOrder.Content COMO SalesOrderContent
         DONDE SalesOrderContent.Ref = &Document
         AGRUPAR POR SalesOrderContent.Items) COMO Content
             UNIRSE AccumulationRegister.ItemAccounting.Balance(,
                 Items EN (
                     SELECCIONAR 
                         Document.SalesOrder.Content.Items 
                     DESDE
                         Document.SalesOrder.Content 
                     DONDE
                         Document.SalesOrder.Content.Ref = &Document)
                 ) COMO ItemAccountingBalances
             EN ItemAccountingBalances.Items = Content.Items
DONDE
    (ItemAccountingBalances.QuantityBalance < Content.Quantity 
     O (ItemAccountingBalances.QuantityBalance) ES NULO )

Luego, debe mostrar el campo Presentation al usuario.

Ejemplo:

Resultado = Consulta.Ejecutar();
Si No Resultado.EstaVacio() Entonces
    Mensaje("Productos insuficientes:");
    Seleccion = Resultado.Elegir();
    Mientras Seleccion.Siguiente() Hacer
        Mensaje(Seleccion.Presentacion + ": requerido: " + Fila(Seleccion.Cantidad)
                  + ", disponible: " + Fila(Seleccion.SaldoCantidad));
    FinHacer;
FinSi;

Conclusión

Esta sección describe algunas especificidades de la verificación de los saldos de productos. Hemos proporcionado ejemplos de consultas para obtener los datos necesarios para verificar los saldos. Puede utilizar estos ejemplos cuando implemente consultas reales.

Especificidades de la clasificación por campos de referencia

Cuando trabaje con consultas, a veces es necesario ordenar los resultados de la consulta por la presentación de algún campo de referencia. Por ejemplo, es posible que necesite ordenar una lista de productos por presentación. Tenga en cuenta que ordenar directamente por el campo Presentación conduce a un resultado algo inesperado: los datos se ordenan según el orden ascendente de las referencias de objetos. La razón de este efecto es el hecho de que el campo Presentación es virtual y a nivel de base de datos consta de varios campos. El valor de presentación se forma en función de estos campos en el momento en que se recupera. Si se realiza la clasificación por este campo, se aplica la clasificación por los campos reales que lo forman consecuentemente, y el resultado se ordena por la referencia interna del campo en lugar de por la presentación en forma de cadena.

Ejemplo de clasificación incorrecta:

SELECCIONAR
    Articulos.Codigo,
    Articulos.Descripcion,
    Articulos.PrecioCompra
DESDE
    Catalogo.Articulos COMO Articulos
DONDE
    Articulos.EsCarpeta = FALSO
ORDENAR POR
    Articulos.Presentacion

En realidad, la clasificación por presentación es cuestionable en términos de la utilidad del resultado. Por ejemplo, echemos un vistazo a las presentaciones de los documentos: estas cadenas contienen fechas, y ordenar fechas como cadenas no proporciona ningún resultado significativo. Para asegurarse de que la clasificación cumpla con las expectativas del usuario, utilice la clasificación directamente por el campo de referencia y agregue la palabra clave AUTOORDEN a la consulta. En este caso, una consulta recupera los campos reales para cada campo de referencia necesario para ordenarlo (fecha y número para documentos, presentación predeterminada para catálogos) y aplica la clasificación por estos campos reales.

Ejemplo de clasificación correcta:

SELECCIONAR
    Articulos.Codigo,
    Articulos.Descripcion,
    Articulos.PrecioCompra
DESDE
    Catalogo.Articulos COMO Articulos
DONDE 
    Articulos.EsCarpeta = FALSO
ORDENAR POR
    Articulos.Ref
AUTOORDEN

Especificidades de la función DATEDIFF del lenguaje de consulta

La función DATEDIFF del lenguaje de consulta se utiliza para obtener la diferencia entre dos fechas en una unidad de medida especificada. La sintaxis de la función es la siguiente:

DATEDIFF(<Expresión>, <Expresión>, Segundo | Minuto | Hora | Día | Mes | Trimestre | Año)

El valor del primer parámetro se resta del valor del segundo parámetro.

El tercer parámetro define la unidad de medida para el resultado de la función. Puede utilizar los siguientes valores en el tercer parámetro: Segundo, Minuto, Hora, Día, Mes, Trimestre y Año. Al calcular el resultado, la función ignora las unidades de medida que son más pequeñas que la especificada en este parámetro.

Por ejemplo, si especifica Hora en el tercer parámetro, se ignoran los minutos y segundos en los dos primeros parámetros. Si especifica Mes en el tercer parámetro, se ignoran los días, horas, minutos y segundos.

La siguiente tabla proporciona ejemplos de los valores de retorno de la función:

Función	Resultado
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), SEGUNDO)	82 652
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), MINUTO)	1 378
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), HORA)	23
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), DÍA)	1
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), MES)	1
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), TRIMESTRE)	1
DATEDIFF(FECHA(2002, 12, 31, 10, 20, 34), FECHA(2003, 01, 01, 9, 18, 06), AÑO)	1
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), SEGUNDO)	31 535 999
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), MINUTO)	525 599
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), HORA)	8 759
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), DÍA)	364
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), MES)	11
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), TRIMESTRE)	3
DATEDIFF(FECHA(2002, 01, 01, 0, 0, 0), FECHA(2002, 12, 31, 23, 59, 59), AÑO)	0

Especificaciones de la ejecución de la función SUBSTRING() del lenguaje de consulta

Esta sección cubre las especificaciones de la ejecución de la función SUBSTRING() del lenguaje de consulta en el modo cliente/servidor y los consejos para construir consultas con esta función.

Función SUBSTRING()

En el lenguaje de consulta de 1C:Enterprise, la función SUBSTRING() tiene el siguiente formato: SUBSTRING(<Cadena de origen>, <Punto de inicio>, <Longitud>). Se aplica a datos de tipo cadena. Selecciona una parte de la <Cadena de origen> que comienza en el carácter de la posición <Punto de inicio> (se considera que el primer carácter tiene el número 1) y tiene una longitud de <Longitud> caracteres. El resultado de la función SUBSTRING() es una cadena de longitud variable y se considera ilimitada si la <Cadena de origen> tiene longitud ilimitada y el parámetro <Longitud> no es una constante o es mayor que 1024.

Cálculo de la función SUBSTRING() en un servidor SQL

En el modo cliente/servidor, la función SUBSTRING() se implementa utilizando la función SUBSTRING() del operador SQL correspondiente que se pasa al servidor de base de datos (SQL Server). El servidor calcula el tipo de resultado de la función SUBSTRING() utilizando reglas sofisticadas que tienen en cuenta el tipo y los valores de sus parámetros, así como el contexto en el que se utiliza.

En la mayoría de los casos, estas reglas no afectan la ejecución de la consulta de 1C:Enterprise, pero a veces la longitud máxima de la cadena resultante calculada por el SQL Server es importante para la ejecución de la consulta. Tenga en cuenta que en algunos escenarios de uso de la función SUBSTRING(), la longitud máxima de su resultado puede ser igual al número máximo de la cadena de longitud limitada, que es de 4000 caracteres en SQL Server. Esto puede resultar en la terminación inesperada de la ejecución de la consulta.

Por ejemplo, la siguiente consulta:

SELECT
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN SUBSTRING(Presentation, 0, 200) 
        ELSE NULL 
    END AS Presentación,
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN SUBSTRING(Presentation, 0, 200) 
        ELSE NULL 
    END AS Presentación1
FROM
    InformationRegister.ContactInfo AS ContactInfo
ORDER BY
    Presentación,
    Presentación1

se termina con el siguiente mensaje:

Error de DBMS:
Proveedor Microsoft OLE DB para SQL Server: Advertencia: El procesador de consultas no pudo generar un plan de consulta a partir del optimizador porque la longitud total de todas las columnas en la cláusula GROUP BY o ORDER BY excede los 8000 bytes.
HRESULT=80040E14, SQLSTATE=42000, nativo=8618

Esto se debe a que la longitud máxima de cadena se calcula por Microsoft SQL Server utilizando la siguiente expresión:

CASE
    WHEN Type = &LegalAddressOfIndividual 
    THEN SUBSTRING(Presentation, 0, 200) 
    ELSE NULL 
END AS Presentación

es igual a 4000 caracteres. Por eso, la longitud de un registro que contiene dos campos de este tipo es mayor a los 8000 bytes permitidos para ejecutar la operación de ordenamiento.

Debido a este comportamiento de la función SUBSTRING() en SQL Server, recomendamos no utilizar la función SUBSTRING() para convertir cadenas de longitud ilimitada a cadenas de longitud limitada. Recomendamos utilizar CAST() en su lugar. Por lo tanto, el ejemplo anterior se puede reescribir de la siguiente manera:

SELECT
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN CAST(Presentation AS String(200)) 
        ELSE NULL 
    END AS Presentación,
    CASE
        WHEN Type = &LegalAddressOfIndividual 
        THEN CAST(Presentation AS String(200)) 
        ELSE NULL 
    END AS Presentación1
FROM
    InformationRegister.ContactInfo AS ContactInfo
ORDER BY 
    Presentación,
    Presentación1

Especificaciones del uso de la cláusula “IN HIERARCHY” en el lenguaje de consulta

Cuando se utiliza la cláusula IN HIERARCHY en el lenguaje de consulta, siempre recuerde que si pasa una referencia nula al parámetro, la ejecución de la consulta se ralentizará porque el sistema verificará cada elemento para determinar si pertenece a la raíz de la jerarquía. Para evitar esto, la condición IN HIERARCHY solo debe usarse con un elemento (grupo) especificado, por lo que es mejor hacer que estos campos sean obligatorios. Si el parámetro no está especificado (es una referencia nula), es mejor eliminar esta condición por completo de la consulta.

Acceso a campos desde otros campos utilizando . (punto)

En el lenguaje de consulta, puede acceder a un campo especificándolo después de otro campo, separados por . (punto). Por un lado, esto le permite crear consultas cortas. Pero por otro lado, siempre debe comprender el contexto de cada acción de este tipo. Esta sección cubre el acceso a campos utilizando . (punto) y proporciona consejos sobre cómo optimizar el acceso a los campos.

Acceso a un campo utilizando . (punto)

Consideremos la siguiente consulta:

SELECT
    Items.Description, 
    QuantityTurnover
FROM 
    AccumulationRegister.ItemsAccounting.Turnovers AS ItemsAccountingTurnovers

En esta consulta, el campo Description se accede agregándolo después de . (punto) al campo Items. Cuando se ejecuta esta consulta, el lenguaje de consulta de 1C:Enterprise genera una conexión implícita al catálogo Items y obtiene el campo Description de él. Por lo tanto, la consulta que se ejecuta realmente tiene el siguiente formato:

SELECT
    CatalogItems.Description, 
    QuantityTurnover 
FROM
    AccumulationRegister.ItemsAccounting.Turnovers AS ItemsAccountingTurnovers  
    LEFT JOIN Catalog.Items AS CatalogItems 
    ON ItemsAccountingTurnovers.Items = CatalogItems.Ref

Tenga en cuenta que si una consulta obtiene múltiples campos de un solo campo utilizando . (punto), la plataforma crea una única conexión a la tabla referenciada por estos campos. Por ejemplo, si en el primer ejemplo obtiene los campos Description y Code del campo Items utilizando . (punto), se crea una única conexión a la tabla Items en lugar de dos conexiones.

Accediendo a un campo Ref utilizando . (punto)

No obtenga el campo Ref de otro campo utilizando . (punto). Cuando obtiene el campo Ref utilizando este método, se crea una conexión adicional, lo que probablemente afectará el rendimiento de la consulta.

Por ejemplo, en la siguiente consulta se accede al campo Ref desde el campo Counterparty utilizando . (punto).

SELECT
    PurchaseOrder.Date,
    PurchaseOrder.Number
FROM
    Document.PurchaseOrder AS PurchaseOrder
WHERE
    PurchaseOrder.Counterparty.Ref = &Counterparty

Esto resulta en la creación de una conexión redundante a la tabla Counterparties, lo que probablemente ralentizará la ejecución de la consulta. Esta consulta debe modificarse de la siguiente manera:

SELECT
    PurchaseOrder.Date,
    PurchaseOrder.Number
FROM
    Document.PurchaseOrder AS PurchaseOrder
WHERE
    PurchaseOrder.Counterparty = &Counterparty

Utilizando . (punto) para acceder a campos de los campos que hacen referencia a múltiples objetos

Si un campo utilizado para acceder a otro campo es una referencia a múltiples tablas, el número de conexiones establecidas durante la ejecución de la consulta es igual al número de tablas referenciadas que contienen el campo requerido.

Ejemplo: Supongamos que el campo Recorder del registro de acumulación hace referencia a dos documentos: PurchaseOrder y SalesOrder. En este caso, la siguiente consulta:

SELECT
    ItemsAccounting.Recorder.Number,
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting

resultará en una conexión implícita a ambas tablas de documentos, es decir, se establecerán dos conexiones. La consulta ejecutada se verá de la siguiente manera:

SELECT
    CASE
        WHEN ItemsAccounting.Recorder REFS Document.SalesOrder
            THEN SalesOrder.Number
        WHEN ItemsAccounting.Recorder REFS Document.PurchaseOrder
            THEN PurchaseOrder.Number 
    END, 
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting
        LEFT JOIN Document.SalesOrder AS SalesOrder 
        ON ItemsAccounting.Recorder = SalesOrder.Ref
        LEFT JOIN Document.PurchaseOrder AS PurchaseOrder
        ON ItemsAccounting.Recorder = PurchaseOrder.Ref

Pero si el campo utilizado para acceder a otro campo tiene tipo AnyRef, en algunos casos se puede establecer una conexión implícita con todas las tablas de objetos en la configuración. Entonces, por ejemplo, si utiliza un campo del tipo AnyRef para obtener el campo Presentation y la configuración tiene 50 catálogos y 100 documentos, se establecerán 150 conexiones a diferentes tablas.

Debe tener esto en cuenta al diseñar su estructura de datos, así como al crear consultas.

Utilizando CAST para limitar el número de conexiones al acceder a campos utilizando . (punto)

Cuando genera una consulta, a veces sabe qué referencia estará presente en un campo específico que hace referencia a múltiples tablas. En este caso, puede especificar explícitamente la tabla requerida en la consulta para que se establezca una conexión implícita solo con la tabla especificada en lugar de todas las tablas referenciadas por este campo.

Ejemplo: Supongamos que el campo Recorder del registro de acumulación hace referencia a dos documentos: PurchaseOrder y SalesOrder, y la declaración WHERE limita la selección a los valores de recorder que hacen referencia a PurchaseOrder:

SELECT
    ItemsAccounting.Recorder.Number,
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting 
WHERE
    ItemsAccounting.Recorder REFS Document.PurchaseOrder

En relación con esta consulta, como sabemos con certeza que el resultado incluirá solo los documentos con el recorder que hace referencia a la tabla de órdenes de compra, puede modificar la consulta para mejorar su rendimiento:

SELECT
    CAST(ItemsAccounting.Recorder AS Document.PurchaseOrder).Number,
    ItemsAccounting.Count
FROM
    AccumulationRegister.ItemsAccounting AS ItemsAccounting
WHERE
    ItemsAccounting.Recorder REFS Document.PurchaseOrder

Cuando se ejecuta esta consulta, solo se establece la conexión con la tabla Document.PurchaseOrder en lugar de conectarse a ambas tablas referenciadas por el campo Recorder. Esto mejora el rendimiento de la consulta.

Consultas de ejemplo para operaciones con catálogos jerárquicos

Esta sección describe las soluciones para tareas típicas relacionadas con catálogos jerárquicos.

Obtener elementos de un catálogo jerárquico que son subordinados a una carpeta especificada

La cláusula IN HIERARCHY del lenguaje de consulta está destinada a obtener elementos subordinados de un catálogo jerárquico. Ejemplo:

SELECT
    Items.Code,
    Items.Description AS Descripción,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items
WHERE
    Items.Ref IN HIERARCHY(&Folder)

Este ejemplo obtiene todos los elementos del catálogo Items que pertenecen a la carpeta &Folder, incluyendo la carpeta misma, sus carpetas subordinadas y los elementos en estas carpetas subordinadas.

Si solo desea obtener elementos y carpetas subordinadas a una carpeta específica, puede obtenerlos aplicando una condición al campo Parent. Ejemplo:

SELECT
    Items.Code,
    Items.Description AS Descripción,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items
WHERE
    Items.Parent = &Folder

Esta consulta selecciona elementos y carpetas que son subordinados a la carpeta con referencia &Folder.

Verificar la disponibilidad de elementos subordinados para un elemento de catálogo

Para verificar si un elemento de catálogo tiene elementos subordinados, puede utilizar la siguiente consulta:

SELECT TOP 1
    Items.Ref
FROM
    Catalog.Items AS Items
WHERE
    Items.Parent = &Parent

En este ejemplo, la referencia al elemento que se debe verificar para la disponibilidad de elementos subordinados se registra en el parámetro de consulta Parent. Después de la ejecución de la consulta, debe verificar si el resultado está vacío. Los registros subordinados están disponibles si el resultado no está vacío. De lo contrario, no están disponibles. Ejemplo:

If Query.Execute().IsEmpty() Then
    Message("No hay registros");
Else 
    Message("Hay registros disponibles");
EndIf;

Obtener todos los padres de un elemento

El lenguaje de consulta no ofrece medios especiales para obtener todos los padres de un elemento. Puede implementar esto utilizando totales jerárquicos, pero obtener totales jerárquicos está optimizado para construir los totales de un gran número de registros y no es particularmente eficiente para obtener los padres de un solo elemento. Para obtener todos los registros padres de un elemento de manera más eficiente, recomendamos recorrer sus padres en lotes pequeños en un bucle.

Ejemplo:

CurrentItemsItem = ItemsItem;

Query = New Query("SELECT 
                      | Items.Parent, 
                      | Items.Parent.Parent, 
                      | Items.Parent.Parent.Parent, 
                      | Items.Parent.Parent.Parent.Parent, 
                      | Items.Parent.Parent.Parent.Parent.Parent 
                      |FROM 
                      | Catalog.Items AS Items 
                      |
                      |WHERE 
                      | Items.Ref = &CurrentItemsItem";

While True Do
    Query.SetParameter("CurrentItemsItem", CurrentItemsItem); 
    Result = Query.Execute(); 
    If Result.IsEmpty() Then 
        Break; 
    EndIf; 
    Selection = Result.Choose(); 
    Selection.Next(); 
    For ColumnNumber = 0 To Result.Columns.Count() - 1 Do 
        CurrentItemsItem = Selection[ColumnNumber]; 
        If CurrentItemsItem = Catalogs.Items.EmptyRef() Then 
            Break; 
        Else 
            Message(CurrentItemsItem); 
        EndIf; 
    EndDo; 

    If CurrentItemsItem = Catalogs.Items.EmptyRef() Then 
        Break; 
    EndIf;
EndDo;

En este ejemplo, se muestran todos los padres para la referencia almacenada en la variable ItemsItem en la ventana de mensajes. Se seleccionan 5 padres en cada paso del bucle.

Si el número de niveles en un catálogo es limitado y no es grande, puede obtener todos los padres en una sola consulta sin usar un bucle.

Mostrar un catálogo jerárquico en un informe

Para mostrar un catálogo jerárquico en un informe manteniendo la jerarquía, utilice el siguiente formato de consulta:

SELECT
    Items.Code,
    Items.Description AS Descripción,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items 
ORDER BY
    Descripción HIERARCHY

Esta consulta selecciona todos los registros de un catálogo y los ordena por jerarquía. El resultado se ordena por nombre teniendo en cuenta la jerarquía.

Para que las carpetas del catálogo estén por encima de los elementos, reemplace la cláusula ORDER BY con lo siguiente en esta consulta:

ORDER BY
    Items.IsFolder HIERARCHY,
    Description

El resultado seguirá ordenado por jerarquía, pero las carpetas estarán ubicadas por encima de los elementos.

También es posible reemplazar la cláusula ORDER BY con la cláusula AUTOORDER. Entonces, el resultado se ordenará según la configuración del catálogo, es decir, si la configuración del catálogo especifica que las carpetas deben estar por encima de los elementos, estarán por encima.

También puede obtener la estructura jerárquica de un catálogo utilizando totales.

Ejemplo:

SELECT
    Items.Code, 
    Items.Description AS Description, 
    Items.PurchasePrice 
FROM Catalog.Items AS Items
WHERE 
    (Items.IsFolder = FALSE)
ORDER BY Description
TOTALS BY Items.Ref ONLY HIERARCHY

Obteniendo totales por jerarquía

Para obtener los totales por jerarquía, en la cláusula TOTALS BY agregue la palabra clave HIERARCHY después de especificar el campo utilizado para calcular los totales. Aquí hay un ejemplo de un informe de “Rotación de artículos” donde se obtienen los totales por jerarquía:

SELECT
    ItemAccountingTurnovers.Items AS Items,
    ItemAccountingTurnovers.Items.Presentation,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers AS ItemAccountingTurnovers
TOTALS SUM(QuantityTurnover) BY
    Items HIERARCHY

Esta consulta calcula los totales no solo para cada artículo, sino también para las carpetas que contienen los artículos.

Si no necesita totales por artículos y solo necesita totales por carpetas, use la cláusula ONLY HIERARCHY en los totales. Ejemplo:

SELECT
    ItemAccountingTurnovers.Items AS Items,
    ItemAccountingTurnovers.Items.Presentation,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers AS ItemAccountingTurnovers
TOTALS SUM(QuantityTurnover) BY
    Items ONLY HIERARCHY

La consulta devuelve los totales para las carpetas de artículos solamente.

Bit de los resultados de expresiones y funciones de agregado en el lenguaje de consulta

Cuando las consultas utilizan operaciones de datos y funciones con una definición que incluye la especificación de longitud (String, Number) y precisión (Number), es posible que deba tener en cuenta las reglas para calcular la longitud y precisión de los resultados. Esta sección cubre estas reglas.

Operaciones de cadena

La descripción de los datos de tipo String puede incluir las siguientes especificaciones adicionales: longitud (número máximo de caracteres), longitud permitida (fija o variable), longitud ilimitada (False o True). Esta tabla enumera las reglas para calcular las especificaciones adicionales de los resultados de las funciones en función de las especificaciones adicionales de sus operandos de cadena.

Función	Longitud ilimitada
	True	False, longitud = n
	True	Fija	Variable
SUBSTRUNG(String, m, k)	Longitud ilimitada	Longitud k, variable	Longitud k, variable
MIN(String)	Longitud ilimitada	Longitud n, fija	Longitud n, variable
Max(String)	Longitud ilimitada	Longitud n, fija	Longitud n, variable

La siguiente tabla cubre las reglas utilizadas para determinar las especificaciones adicionales de la operación “+” (concatenación de cadenas) que toma dos operandos de cadena.

Operando 1		Operando 2
		Longitud ilimitada	Longitud = m
		Longitud ilimitada	Fijo	Variable
Longitud ilimitada		Longitud ilimitada	Longitud ilimitada	Longitud ilimitada
Longitud n	Fijo	Longitud ilimitada	Longitud n + m, fijo	Longitud n + m, variable
Longitud n	Variable	Longitud ilimitada	Longitud n + m, variable	Longitud n + m, variable

Operaciones con números

Cuando realiza varias conversiones de datos numéricos, tenga en cuenta que un número puede contener (partes enteras y fraccionarias combinadas) un máximo de 38 dígitos. 1C:Enterprise utiliza números decimales con punto fijo. Esto significa que el número 34.28 contiene 4 dígitos, y el número 0.000000001 tiene 9 dígitos (el 0 antes del punto no se considera un dígito), mientras que el número 3200000000 tiene 10 dígitos. La cantidad de bits de los resultados de varias operaciones se calcula para excluir posibles desbordamientos aritméticos y, al mismo tiempo, obtener el resultado más preciso. Durante el desarrollo de la configuración, la selección de la cantidad de bits de los campos de los objetos de metadatos debe basarse en la cantidad de bits posibles de los datos almacenados en dichos campos y no debe tener en cuenta ningún posible aumento de bits debido a operaciones y funciones aritméticas.

“+” (suma), “-” (resta)

Las operaciones de suma y resta siempre se ejecutan con precisión en el lenguaje de consulta. Para esto, la longitud de la parte fraccionaria del resultado se determina como la longitud máxima de las partes fraccionarias de todos los operandos. La longitud de la parte entera del resultado se define como la longitud máxima de las partes enteras de los operandos más 1. Si en este caso el número total de dígitos en las partes enteras y fraccionarias supera los 38, el número de dígitos se reduce a 38 reduciendo el número de dígitos en la parte fraccionaria, pero no menos de 10.

“*” (multiplicación)

Las operaciones de multiplicación se ejecutan con precisión si al menos uno de los operandos es un número entero. La longitud de la parte fraccionaria del resultado se determina como la longitud máxima de las partes fraccionarias de los operandos, pero no menos de 10 dígitos. La longitud de la parte entera del resultado se calcula como la suma de las longitudes de las partes enteras de los operandos. Si el número total de dígitos en las partes enteras y fraccionarias supera los 38, se reduce a 38.

“/” (división)

La operación de división solo se ejecuta con precisión si ambos operandos son números enteros y el primer operando se puede dividir por el segundo sin dejar nada. La longitud de la parte fraccionaria del resultado se determina como la longitud máxima de las partes fraccionarias de los operandos, pero no menos de 10 dígitos. La longitud de la parte entera del resultado es igual a la suma de las longitudes de la parte entera del dividendo y la parte fraccionaria del divisor. Si en este caso el número total de dígitos en las partes enteras y fraccionarias supera los 38, este número se reduce a 38 reduciendo el número de dígitos en la parte fraccionaria, pero no menos de 10.

SUMA

La función de agregación SUMA se calcula con la mayor precisión posible. El número de dígitos fraccionarios del resultado es igual al número de dígitos fraccionarios del operando. El número de dígitos en la parte entera se incrementa en 7. Si en este caso el número total de dígitos en las partes enteras y fraccionarias supera los 38, este número se reduce a 38 reduciendo el número de dígitos en la parte fraccionaria, pero no menos de 10.

MÍN, MÁX, PROM

Los resultados de las funciones MÍN, MÁX y PROM tienen el mismo número de dígitos en las partes enteras y fraccionarias que sus operandos.

COUNT, AÑO, TRIMESTRE, MES, DÍA DEL AÑO, DÍA, SEMANA, DÍA DE LA SEMANA, HORA, MINUTO, SEGUNDO, DIFERENCIA DE FECHAS

Las funciones enumeradas tienen un resultado numérico con 10 dígitos en la parte entera. No hay parte fraccionaria.

NOTAS:

El número total de dígitos en un número es igual a la suma de los dígitos en su parte entera y fraccionaria. Por ejemplo, si siguiendo las reglas anteriores el resultado tiene 4 dígitos en la parte entera y 3 dígitos en la parte fraccionaria, su descripción de tipo es Número(7, 3)
En el modo cliente/servidor, el número de dígitos en la parte fraccionaria de la operación de división (“/”) está limitado a 6 en lugar de 10. Esto se debe a las especificidades de la aritmética decimal en Microsoft SQL Server.
Si considera razonable cambiar las reglas de cálculo para la longitud y precisión del resultado para operaciones con datos, utilice el operador CAST para especificar la longitud y precisión requeridas. Tenga en cuenta que si desea cambiar la longitud y precisión de un resultado de operación, el operador CAST debe aplicarse a su operando. En este caso, la longitud y precisión del resultado de la operación se calculan utilizando las reglas anteriores basadas en la longitud y precisión del operando especificado por CAST. Por ejemplo, si el campo “Atributo1” es de tipo “Número(8, 0)”, para que la expresión Atributo1 / 3 tenga 15 dígitos en la parte fraccionaria, utilice el siguiente código: CAST(Atributo1 AS Número(23, 15)) / 3

Calculando totales por campos de saldo

Las tablas virtuales BalanceAndTurnovers de registros de acumulación y registros contables le permiten obtener saldos para un período deseado. El lenguaje de consulta de 1C:Enterprise proporciona el cálculo automático de totales para agrupaciones por campos de saldo. Este artículo cubre cómo se calculan los totales por campos de saldo y muestra algunas de las especificidades de dichos cálculos.

Totales por campos de saldo

Para calcular los totales por un campo de saldo, es suficiente obtener el campo en una lista de selección de consulta y especificar que los totales deben ser calculados por este campo.

Ejemplo:

SELECT
    Almacén AS Almacén,
    Artículos AS Artículos,
    Período AS Período,
    CantidadSaldoInicial AS CantidadSaldoInicial,
    CantidadMovimiento AS CantidadMovimiento,
    CantidadSaldoFinal AS CantidadSaldoFinal
FROM
    RegistroAcumulación.ArtículosContabilidad.BalanceAndTurnovers(, , Semana, , ) AS ArtículosContabilidadBalanceAndTurnovers
TOTALS
    SUM(CantidadSaldoInicial), SUM(CantidadMovimiento), SUM(CantidadSaldoFinal)
POR
    Almacén,
    Período,
    Artículos

Durante la ejecución de la consulta, la plataforma identifica automáticamente el campo CantidadSaldoInicial como el que contiene el saldo inicial y el campo CantidadSaldoFinal como el que contiene el saldo final, y luego aplica el algoritmo para calcular los totales por estos campos.

Algoritmo de cálculo de totales por campos de saldo

Para que una consulta pueda calcular los totales por los campos de saldo, la consulta debe obtener ambos valores del saldo para un período: el saldo inicial y el saldo final. Cuando una consulta obtiene solo uno de los dos saldos y este valor se utiliza para calcular los totales, la plataforma complementa implícitamente la consulta obteniendo el campo con el otro saldo.

El algoritmo para calcular los totales por saldos consta de tres pasos:

cálculo de totales para una agrupación obtenida antes de la agrupación por período;
cálculo de totales para la agrupación por período;
cálculo de totales para una agrupación obtenida después de la agrupación por período.

Cuando se calculan los totales para una agrupación obtenida antes de la agrupación por período, los registros se ordenan por los campos de primer nivel que aún no se han agrupado y por el campo de período, seguido de la iteración de registros. Los primeros registros para una combinación de los campos de primer nivel se utilizan para sumar el saldo inicial, mientras que los últimos registros se utilizan para calcular el saldo final.

Cuando se calculan los totales para la agrupación por período, los registros se ordenan por período, se iteran los registros para cada fecha y se calcula la suma para varias combinaciones de valores de campo de primer nivel (aquellos que no se obtienen utilizando .(punto) desde otro campo en una lista de selección) que aún no se han utilizado para obtener una selección para la fecha que es igual o mayor que la calculada para el saldo inicial y menor o igual que la calculada para el saldo final. Tenga en cuenta que cuando se selecciona un campo de una tabla vinculada para un resultado de consulta, los saldos para el período se calculan teniendo en cuenta las combinaciones de valores para este campo.

Cuando se calculan los totales por una agrupación dentro de una agrupación por período, el cálculo se realiza de la misma manera que para los campos regulares, simplemente sumándolos.

Usando campos de saldo combinados con otros campos

Si se utiliza un campo de saldo en una expresión, para cada saldo inicial la plataforma intenta encontrar una expresión similar que contenga un saldo final. Si no se encuentra la expresión, el campo se agrega implícitamente a la consulta.

Ejemplo:

SELECT
    Almacén AS Almacén,
    Artículos AS Artículos,
    Período AS Período,
    CantidadSaldoInicial * Artículos.PrecioCompra AS CantidadSaldoInicial,
    CantidadMovimiento * Artículos.PrecioCompra AS CantidadMovimiento,
    CantidadSaldoFinal * Artículos.PrecioCompra AS CantidadSaldoFinal
FROM
    RegistroAcumulativo.ArtículosContabilidad.SaldoYMovimientos(, , Semana, , ) AS ArtículosContabilidadSaldoYMovimientos
TOTALS
    SUM(CantidadSaldoInicial), SUM(CantidadMovimiento), SUM(CantidadSaldoFinal)
BY
    Almacén,
    Período,
    Artículos

Si una expresión utiliza múltiples campos de saldo inicial o saldo final, la plataforma no reconoce esta expresión como una expresión de saldo y calcula los totales por esta expresión utilizando una simple suma.

Calculando totales por registrador

Si necesita obtener totales para saldos por un registrador de registro, tenga en cuenta que solo puede obtener totales por el registrador dentro de la agrupación por Período. Esta característica se debe al hecho de que un registrador es una elaboración de un período. Si se obtienen totales por un registrador antes que por un período, los totales resultantes son incorrectos. Un enfoque similar se aplica para obtener totales por número de fila: dichos totales solo se pueden obtener dentro de una agrupación por período y registrador.

Totales por atributo de registrador

Es imposible calcular correctamente los totales por un atributo de un registrador. Si necesita incluir saldos con una especificación del atributo de registrador en los resultados, debe crear una agrupación por registrador y solo incluir el atributo de registrador en el resultado, sin incluir el registrador en sí.

Contenido del campo de catálogo

Los atributos de un catálogo jerárquico que tienen la propiedad Usar establecida en Para elemento contienen valores NULL en los registros que son grupos. De manera similar, aquellos atributos que tienen la propiedad Usar establecida en Para carpeta contienen NULL en los registros que son elementos de catálogo. Tenga esto en cuenta al escribir consultas a catálogos.

Ejemplo:

SELECT
 DescripciónArtículos, 
 EsCarpetaArtículos, 
 NúmeroExistenciasArtículos, 
 CASE 
  WHEN (NúmeroExistenciasArtículos) IS NULL 
   THEN "NULL" 
  ELSE "NO NULL" 
 END 
FROM 
 Catálogo.Artículos AS Artículos

Resultado:

Nombre	EsCarpeta	Número de stock	Campo1
Cigarrillos	verdadero		NULL
Peter I lights	falso	18008	NO NULO
Alimentos	verdadero		NULL
Dulces	verdadero		NULL
Trufas	falso	SN-999999	NO NULO
Bastones de caramelo	falso	SN-6666888	NO NULO
Ositos de goma	falso	SN-6666	NO NULO
Bombones de menta	falso	SN-7777	NO NULO
Frutitas	falso	SN-999001	NO NULO
Comestibles	verdadero		NULL

Este ejemplo muestra que, para todos los registros de grupo, el valor del atributo Número de stock contiene NULL.

Testimonios y Casos de Estudio

Recorrido del Producto

Lenguaje de consulta

Selección de todos los valores pertenecientes a un grupo de un resultado de consulta

Visualización de campos de referencia

Complementando el resultado de la consulta por días pertenecientes a un período especificado

Uso de filtros en consultas con tablas virtuales

Uso de la cláusula FOR UPDATE en el lenguaje de consulta

Uso de expresiones personalizadas en totales del lenguaje de consulta

Expresiones personalizadas en totales

Ejemplos de uso

Cálculo de expresiones basadas en funciones de agregado

Cálculo de totales solo para agrupaciones recuperadas

Redefinir presentaciones de puntos de agrupación

Ordenamiento en consultas anidadas

Uso de la función ISNULL()

La función ISNULL

Uso de la operación CASE

Especificidades de la función ISNULL

Detalles sobre cómo usar el operador de inclusión de grupo

Operador de inclusión de grupo

Implementación del operador de inclusión de grupo

Notas sobre la velocidad de ejecución

Tablas cortas

Índices

Tablas temporales

Especificaciones del uso de la cláusula GROUP BY

Especificaciones del trabajo con la tabla virtual de datos de programación del registro de cálculo

Especificos del trabajo con el campo Presentación y la función Presentación() del lenguaje de consulta

Campo Presentación

Función Presentation()

Especificaciones de conexión a la tabla de saldos virtuales

Introducción

Verificación de saldos

Conclusión

Especificidades de la clasificación por campos de referencia

Especificidades de la función DATEDIFF del lenguaje de consulta

Especificaciones de la ejecución de la función SUBSTRING() del lenguaje de consulta

Función SUBSTRING()

Cálculo de la función SUBSTRING() en un servidor SQL

Especificaciones del uso de la cláusula “IN HIERARCHY” en el lenguaje de consulta

Acceso a campos desde otros campos utilizando . (punto)

Acceso a un campo utilizando . (punto)

Accediendo a un campo Ref utilizando . (punto)

Utilizando . (punto) para acceder a campos de los campos que hacen referencia a múltiples objetos

Utilizando CAST para limitar el número de conexiones al acceder a campos utilizando . (punto)

Consultas de ejemplo para operaciones con catálogos jerárquicos

Obtener elementos de un catálogo jerárquico que son subordinados a una carpeta especificada

Verificar la disponibilidad de elementos subordinados para un elemento de catálogo

Obtener todos los padres de un elemento

Mostrar un catálogo jerárquico en un informe

Obteniendo totales por jerarquía

Bit de los resultados de expresiones y funciones de agregado en el lenguaje de consulta

Operaciones de cadena

Operaciones con números

“+” (suma), “-” (resta)

“*” (multiplicación)

“/” (división)

SUMA

MÍN, MÁX, PROM

COUNT, AÑO, TRIMESTRE, MES, DÍA DEL AÑO, DÍA, SEMANA, DÍA DE LA SEMANA, HORA, MINUTO, SEGUNDO, DIFERENCIA DE FECHAS

Calculando totales por campos de saldo

Totales por campos de saldo

Algoritmo de cálculo de totales por campos de saldo

Usando campos de saldo combinados con otros campos

Calculando totales por registrador

Totales por atributo de registrador

Contenido del campo de catálogo

Menú

Contacto

info@axial-erp.co

(+57) 601 5898710

Bogotá Colombia | Estados Unidos