En el artículo:

https://firebird21.wordpress.com/2013/04/28/tablas-agregadas/

habíamos visto lo que son las tablas agregadas y la gran utilidad que podemos obtener de ellas. En éste veremos una forma de crearlas (no la única forma, sino una forma) y también como insertarles datos.

Necesitaremos escribir dos stored procedures para conseguir nuestro objetivo. ¿Por qué? porque el Firebird no nos permite tener un COMMIT dentro de un stored procedure y no podemos insertarle filas a una tabla antes de hacerle un COMMIT exitoso, por lo tanto deberemos crear dos stored procedures:

  • Uno para crear la tabla y su Primary Key
  • Otro para insertarle filas a esa tabla

Ejemplo:

Tenemos una tabla llamada PRODUCTOS, en la cual se guardan el Identificador de cada producto, su Nombre y otros datos. Una tabla llamada MOVIMCAB (cabecera de movimientos) en la cual se guardan el Identificador del movimiento, la Fecha en que ocurrió, el Tipo de Documento que lo avala, el Número del Documento y otros datos. Y una tabla llamada MOVIMDET (detalles de movimientos) en la cual se guarda el Identificador del detalle, el Identificador de la cabecera, el Identificador del producto, la Cantidad vendida y otros datos.

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

En la Captura 1 vemos el Identificador y el Nombre de algunos productos.

AGRUPADAS2

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

Y en la Captura 2 vemos el Identificador del detalle, el Identificador de la cabecera, el Identificador del producto y la Cantidad vendida.

Y lo que queremos obtener es lo siguiente:

AGRUPADAS3

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

En la columna TAB_IDENTI se guarda el Identificador de la cabecera, y en las demás columnas la Cantidad vendida de cada uno de esos productos. Para hacer el ejemplo simple no se agregaron más columnas pero si tú quieres además del Identificador de la cabecera podrías agregarle la Fecha de la Venta, el Tipo de Documento, el Número del Documento, el Nombre del Cliente, etc., o sea que cualquier columna de la tabla MOVIMCAB o sus referenciadas podrías agregar.

Un stored procedure para crear la tabla agregada

CREATE PROCEDURE CREAR_TABLA_AGREGADA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcCreate   VARCHAR(4096);
   DECLARE VARIABLE lcColumna  VARCHAR(28);
BEGIN

 -- Primero, creamos la tabla

   lcCreate = 'CREATE TABLE ' || tcNombreTabla || '(';

   lcCreate = lcCreate || 'TAB_IDENTI INTEGER NOT NULL, ';

   FOR SELECT
      DISTINCT
      P.PRD_NOMBRE AS MOV_NOMPRD
   FROM
      MOVIMDET D
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.PRD_IDENTI
   ORDER BY
      D.MOV_IDECAB
   INTO
      :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcCreate = lcCreate || lcColumna || ' VARCHAR(28), ' ;
   END

   lcCreate = Left(lcCreate, Char_Length(lcCreate) - 2);

   lcCreate = lcCreate || ');';

   EXECUTE STATEMENT lcCreate;

 -- Segundo, le agregamos una Primary Key

   EXECUTE STATEMENT 'ALTER TABLE ' || tcNombreTabla || ' ADD CONSTRAINT PK_' || tcNombreTabla || ' PRIMARY KEY (TAB_IDENTI)';

END;

Este stored procedure crea una tabla con el nombre que quieras asignarle y también una Primary Key para esa tabla. Fíjate que se usa DISTINCT porque los nombres de las columnas no pueden estar repetidos. Y el nombre de la columna se obtiene editando el Nombre del Producto y reemplazando algunos caracteres por guiones bajos. Por supuesto que si no te gustan los guiones bajos podrías usar otros caracteres para el reemplazo.

Un ejemplo de llamada sería el siguiente:

EXECUTE PROCEDURE CREAR_TABLA_AGREGADA('PRUEBA2');


AGRUPADAS5

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

Y así hemos creado una tabla que tiene columnas con ¡¡¡los nombres de los productos!!!. Muy interesante, ¿verdad?. Desde luego que en tu caso podrías poner nombres de alumnos, de profesores, de clientes, de países, de lo que se te ocurra.

Un stored procedure para insertarle filas a la tabla agregada

CREATE PROCEDURE ACTUALIZAR_TABLA_AGREGADA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcUpdate VARCHAR(4096);
   DECLARE VARIABLE lcColumna VARCHAR(28);
   DECLARE VARIABLE lnIdenti INTEGER;
   DECLARE VARIABLE lnCantidad SMALLINT;
BEGIN

   FOR SELECT
      D.MOV_IDECAB,
      P.PRD_NOMBRE,
      SUM(D.MOV_CANTID)
   FROM
      MOVIMDET D
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.PRD_IDENTI
   GROUP BY
      D.MOV_IDECAB,
      P.PRD_NOMBRE
   INTO
      :lnIdenti,
      :lcColumna,
      :lnCantidad
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcUpdate = 'UPDATE OR INSERT INTO ' ||
                 tcNombreTabla || ' (' ||
                 'TAB_IDENTI, ' || lcColumna || ') VALUES (' ||
                 lnIdenti || ', '||
                 lnCantidad || ')';
      EXECUTE STATEMENT lcUpdate;
   END

END;

Aquí se editó el Nombre del Producto de la misma forma que habíamos hecho en el stored procedure CREAR_TABLA_AGREGADA porque los nombres de las columnas deben coincidir. Para poder usar el comando UPDATE OR INSERT se requiere de una Primary Key y por eso la tabla tiene una Primary Key. Este stored procedure debe recibir como parámetro de entrada el mismo nombre que se usó para crear la tabla agregada. Por ejemplo:

EXECUTE PROCEDURE ACTUALIZAR_TABLA_AGREGADA('PRUEBA2')

AGRUPADAS3

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

 Y listo, eso es todo. Escribiendo dos stored procedures similares a los mostrados arriba podrás tener todas las tablas agregadas que quieras. Como recordarás, lo que generalmente se coloca en las tablas agregadas son agrupamiento de datos (sumas, cantidades totales, promedios, máximos, mínimos, etc.) porque sirven para analizar datos, no para verificar datos.

Artículos relacionados:

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

El índice del blog Firebird21