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.
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';
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:
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
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
Comentarios recientes