En el Ejemplo 4. de recursión ya habíamos visto como actualizar una fila y todas sus filas descendientes, ahora veremos otro ejemplo muy útil, el cual nos permitirá obtener los saldos acumulados de forma sencilla y rápida.

UPDATE3

Captura 1. Si haces clic en la imagen la verás más grande

  • CUE_IDENTI es el identificador de cada fila
  • CUE_NUMERO es el número de la cuenta contable
  • CUE_NOMBRE es el nombre de la cuenta contable
  • CUE_IDEPAD es el identificador de la fila padre
  • CUE_ASENTA indica si una cuenta es asentable, también llamada imputable (‘T’) o no lo es (‘F’)
  • CUE_MONTOX es el saldo de la cuenta

Inicialmente, solamente las cuentas asentables (es decir, las que pueden escribirse en el Libro Diario) tienen saldo diferente que cero. Todas las cuentas no asentables tienen saldo cero.

Listado 1.

UPDATE 
   CUENTAS 
SET 
   CUE_MONTOX = 0
WHERE
   CUE_ASENTA = 'F';

UPDATE
   CUENTAS T1
SET
   CUE_MONTOX = CUE_MONTOX + 
                  (WITH RECURSIVE MiCTE AS (

                       SELECT
                          T2.CUE_IDENTI,
                          T2.CUE_MONTOX AS CUE_MONTOX
                       FROM 
                          CUENTAS T2
                       WHERE
                          T2.CUE_IDENTI = T1.CUE_IDENTI

                       UNION ALL

                       SELECT
                          T3.CUE_IDENTI,
                          MiCTE.CUE_MONTOX + T3.CUE_MONTOX AS CUE_MONTOX
                       FROM
                          CUENTAS T3
                       JOIN
                          MiCTE
                             ON T3.CUE_IDEPAD = MiCTE.CUE_IDENTI
                             
                    )
                    
                    SELECT SUM(CUE_MONTOX) FROM MiCTE
                  
                  )
WHERE
   CUE_ASENTA = 'F';

UPDATE4

Captura 2. Si haces clic en la imagen la verás más grande

Como puedes ver, cada cuenta no asentable (CUE_ASENTA = ‘F’) guarda la suma acumulada de sus cuentas hijas. Así por ejemplo “ACTIVO CORRIENTE” es la suma de sus 2 cuentas hijas: “DISPONIBILIDADES” y “CRÉDITOS”. A su vez “DISPONIBILIDADES” es la suma de sus cuentas hijas: “Caja” y “Mi Banco Cta.Cte”. “ACTIVO” tiene una sola cuenta hija, que es “ACTIVO CORRIENTE” entonces sus saldos son idénticos. “CRÉDITOS” tiene una sola cuenta hija, que es “CLIENTES”, por lo tanto sus saldos también son idénticos. En cambio “CLIENTES” tiene tres cuentas hijas y su saldo es la suma de los saldos de sus cuentas hijas.

Explicación:

El primer UPDATE pone en cero los saldos acumulados de todas las cuentas no asentables para no sumar por accidente los saldos que pudieron haberse quedado allí de una ejecución anterior.

El segundo UPDATE lo que hace es sumar al saldo actual de una cuenta, el saldo acumulado de todas sus cuentas hijas. ¿Cómo se halla el saldo acumulado de todas sus cuentas hijas? Mediante la creación de una tabla virtual recursiva llamada MiCTE que le va sumando al saldo actual de una cuenta el saldo de todas sus cuentas hijas.

Recuerda que en la tabla virtual recursiva no se pueden usar las funciones agregadas: COUNT(), MAX(), MIN(), SUM(), AVG() ni tampoco se puede usar la cláusula GROUP BY, por ese motivo se tuvo que escribir: MiCTE.CUE_MONTOX + T3.CUE_MONTOX AS CUE_MONTOX para obtener la suma manualmente.

Fuera de la tabla virtual recursiva sí se puede usar la función SUM(), por eso se la usó en el último SELECT.

Más allá de este caso particular también es importante aprender la técnica de actualizar a una columna de una tabla mediante la creación de una tabla virtual (recursiva o no recursiva). Lo que debemos hacer es lo siguiente: crear a la tabla virtual y luego mediante un SELECT devolver la columna que nos interesa. Lo más frecuente es que la columna que nos interesa se obtenga mediante una función agregada: COUNT(), SUM(), AVG(), MAX(), MIN(), tal y como podemos ver en el último SELECT del Listado 1.

¿Y si queremos ver los saldos acumulados, pero sin actualizar la tabla de CUENTAS?

También es muy sencillo de lograrlo, el algoritmo es casi el mismo.

Listado 2.

SELECT
   T1.CUE_IDENTI,
   T1.CUE_NUMERO,
   T1.CUE_NOMBRE,
   T1.CUE_IDEPAD,
   T1.CUE_ASENTA,
      (WITH RECURSIVE MiCTE AS (
          
          SELECT
             T2.CUE_IDENTI,
             T2.CUE_MONTOX AS CUE_MONTOX
          FROM 
             CUENTAS T2
          WHERE
             T2.CUE_IDENTI = T1.CUE_IDENTI
          
          UNION ALL
          
          SELECT
             T3.CUE_IDENTI,
             MiCTE.CUE_MONTOX + T3.CUE_MONTOX AS CUE_MONTOX
          FROM
             CUENTAS T3
          JOIN
             MiCTE
                ON T3.CUE_IDEPAD = MiCTE.CUE_IDENTI
          
         )
         
         SELECT SUM(CUE_MONTOX) FROM MiCTE
         
   ) AS CUE_MONTOX
FROM
   CUENTAS T1

Y si ejecutas el Listado 2. entonces obtendrás exactamente el mismo resultado que ves en la Captura 2. así que la actualización de la tabla CUENTAS es una posibilidad, pero no es obligatorio hacerlo.

Quizás habrás notado que en el Listado 2. no se pusieron inicialmente a cero los saldos de las cuentas no asentables, como sí se había hecho en el Listado 1. ¿por qué no? porque se supuso que el saldo inicial de cada cuenta no asentable era cero. Pero si ese no es (o podría no ser) el caso entonces también tendrían que haberse cerado los saldos de las cuentas no asentables antes de ejecutar el SELECT, tal y como se hizo en el Listado 1. Como medida preventiva y para asegurarnos de no obtener resultados erróneos, es lo mejor que se puede hacer: cerar los saldos de las cuentas no asentables antes de ejecutar el UPDATE o el SELECT.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

Ejemplo de recursión (4). Actualizando filas recursivamente

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios