Ordenando las columnas variables de las tablas agregadas

Deja un comentario

En artículos anteriores habíamos visto como crear tablas agregadas, de una forma muy fácil y sencilla pero  había un pequeño problema que quizás habrás notado: los nombres de las columnas variables aparecían en orden alfabético.

Y eso a veces puede ser lo que quieres y a veces no. Por ejemplo, si las columnas variables se refieren a nombres de productos, de clientes, de países, de alumnos, etc., entonces está bien mostrarlas ordenadas alfabéticamente, pero si se refieren a nombres de meses eso no es lo usual. No se acostumbra ver a los meses así: ABR, AGO, DIC, ENE, FEB, etc. sino que se acostumbra verlos así: ENE, FEB, MAR, ABR, MAY, etc.

Por ese motivo le hice una pequeña modificación al stored procedure CREAR_TABLA_PIVOT el cual ahora permite especificar el orden en el cual se mostrarán las columnas variables. La nueva versión es la siguiente:

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcTipoDatos              VARCHAR(  64),
   tcOrden                  VARCHAR(  28))
AS
   DECLARE VARIABLE lcCreate       VARCHAR(4096);
   DECLARE VARIABLE lcColumna      VARCHAR(  28);
   DECLARE VARIABLE lcMensajeError VARCHAR(1024);
BEGIN

   -- Primero, creamos la tabla

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

   lcCreate = lcCreate || tcPrimeraColumnaCabecera || ' NOT NULL, ';

   IF (Char_Length(tcOtrasColumnasCabecera) > 0) THEN BEGIN
      tcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',';
      WHILE (Char_Length(tcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(tcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcCreate = lcCreate || Trim(lcColumna) || ' , ' ;
         tcOtrasColumnasCabecera = Replace(tcOtrasColumnasCabecera, lcColumna || ',', '');
      END
   END

   FOR EXECUTE STATEMENT
      'SELECT DISTINCT ' || tcColumnaDatos || ' FROM ' || tcVista || IIF(Char_Length(:tcOrden) > 0, ' ORDER BY ' || :tcOrden, '')
   INTO
      :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcColumna = Replace(lcColumna, '%', '_');
      lcCreate = lcCreate || lcColumna || ' ' || tcTipoDatos || ', ' ;
   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 (' || Left(:tcPrimeraColumnaCabecera, Position(' ', :tcPrimeraColumnaCabecera)) || ')';

END;

donde como puedes ver se agregó el parámetro de entrada tcOrden.

La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   MVC_NOMCLI,
   MVC_NUMANO,
   MVC_NUMMES,
   NOMBRE_MES,
   MVC_NOMCLI1,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      EXTRACT(YEAR  FROM MVC_FECHAX) AS MVC_NUMANO,
      EXTRACT(MONTH FROM MVC_FECHAX) AS MVC_NUMMES,
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
             1, 'ENE',
             2, 'FEB',
             3, 'MAR',
             4, 'ABR',
             5, 'MAY',
             6, 'JUN',
             7, 'JUL',
             8, 'AGO',
             9, 'SEP',
            10, 'OCT',
            11, 'NOV',
            12, 'DIC') AS NOMBRE_MES,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      M.MVC_TOTALX
   FROM
      MOVIMCAB M
   JOIN
      CLIENTES C
         ON M.MVC_IDECLI = C.CLI_IDENTI;

En esta vista tenemos una columna llamada MVC_NUMMES que usaremos para ordenar los nombres de los meses según el número de los meses.

Creando la tabla agregada AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'INTEGER',
      'MVC_NUMMES')

El último parámetro nos indica el orden en el cual queremos ver a las columnas variables. Si no se especifica ese orden entonces las veremos ordenadas alfabéticamente. Como la columna MVC_NUMMES es de tipo numérico y en ella guardamos los números de los meses (1, 2, 3, 4, 5, etc.) entonces los nombres de los meses estarán ordenados según ese criterio y serán ENE, FEB, MAR, ABR, MAY, etc.

Estructura de la tabla AGR_VENTAS_MENSUALES

AGR1

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

Insertándole datos a la tabla AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGR2

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

Y listo, ya obtuvimos lo que queríamos obtener: los nombres de los clientes y a continuación los nombres de los meses. Si además del nombre del mes también quisiéramos tener el número del año sería muy fácil hacerlo: en la vista V_VENTAS reemplazamos la línea:

12, 'DIC') AS NOMBRE_MES,

por:

12, 'DIC') || '/' || EXTRACT(YEAR FROM MVC_FECHAX) AS NOMBRE_MES,

y después de ejecutar el stored procedure CREAR_TABLA_PIVOT esta será la estructura de nuestra tabla AGR_VENTAS_MENSUALES:

AGR3

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

Y después de insertarle datos con el stored procedure ACTUALIZAR_TABLA_PIVOT obtendremos esto:

AGR4

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

Así que ahora el nombre de cada columna variable está compuesto por el nombre del mes y el número del año, generalmente es mejor tenerlas de esta forma para que no se preste a confusión.

Artículos relacionados:

Aplicaciones OLTP y aplicaciones OLAP

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

Creando tablas agregadas e insertándoles datos

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

Ventas mensuales a cada cliente usando tablas agregadas

El índice del blog Firebird21

Anuncios

Ventas mensuales a cada cliente usando tablas agregadas

Deja un comentario

En este artículo hay otro ejemplo del uso de las tablas agregadas. Ahora, las usaremos para ver en columnas las ventas realizadas durante cada mes del año.

NOTA IMPORTANTE:

Los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT han sido levemente modificados. Como seguramente sabes siempre el código fuente puede ser mejorado, y eso es justamente lo que sucedió con ellos. Las más nuevas versiones podrás descargar desde:

http://www.mediafire.com/view/ol58gyejul1t43a/CREAR_TABLA_PIVOT.txt

http://www.mediafire.com/view/1uebv8uerc5kwpe/ACTUALIZAR_TABLA_PIVOT.txt

Necesitarás de las nuevas versiones para que te funcionen los ejemplos que vienen a continuación.

Listado 1. La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   NOMBRE_MES,
   MVC_NOMCLI,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      'MES_' ||
      LPAD(EXTRACT(MONTH FROM MVC_FECHAX), 2, '0') || '_' ||
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
          1, 'ENE',
          2, 'FEB',
          3, 'MAR',
          4, 'ABR',
          5, 'MAY',
          6, 'JUN',
          7, 'JUL',
          8, 'AGO',
          9, 'SEP',
         10, 'OCT',
         11, 'NOV',
         12, 'DIC') AS NOMBRE_MES,
         C.CLI_NOMBRE AS MVC_NOMCLI,
         M.MVC_TOTALX
      FROM
         MOVIMCAB M
      JOIN
         CLIENTES C
            ON M.MVC_IDECLI = C.CLI_IDENTI;

AGREGADAS1

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

Ahora creamos la tabla agregada VENTAS1:

Listado 2. La tabla agregada VENTAS1

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_IDECLI INTEGER',
      'MVC_NOMCLI VARCHAR(40)',
      'NOMBRE_MES',
      'NUMERIC(17, 4)')

AGREGADAS2

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

Y a continuación le agregamos datos:

Listado 3. Insertando filas a la tabla VENTAS1

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_IDECLI INTEGER',
      'MVC_NOMCLI VARCHAR(40)',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGREGADAS3

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

Y listo, ya está. Ya tenemos nuestra tabla agregada con un Cliente por cada fila y las ventas mensuales en las siguientes columnas. Este tipo de tablas puede ser muy útil para la toma de decisiones.

Fíjate que lo único que necesitamos hacer es escribir una vista que contenga los datos que utilizaremos, los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT se encargan de todo lo demás.

Si no queremos ver los Identificadores de los clientes entonces podríamos crear la tabla de esta manera:

Listado 4. La tabla VENTAS1 sin los identificadores de los clientes

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'NUMERIC(17, 4)')

La actualizaríamos así:

Listado 5. Insertando filas a la tabla VENTAS1 sin los identificadores de los clientes

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

Y obtendríamos este resultado:

AGREGADAS4

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

Que es bastante similar al anterior pero no idéntico ¿por qué no? porque en el anterior las filas aparecían ordenadas por Identificador del Cliente y en el actual aparecen ordenadas por Nombre del Cliente. Es decir que las filas siempre aparecen ordenadas por el contenido de la primera columna.

Artículos relacionados:

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

El índice del blog Firebird21

Otro ejemplo de uso de tablas agregadas

Deja un comentario

En este artículo habíamos visto como crear tablas agregadas y como insertarles datos:

https://firebird21.wordpress.com/2013/12/29/creando-y-actualizando-tablas-agregadas-de-uso-general/

Aquí tenemos otro ejemplo, éste está relacionado con los impuestos.

Tenemos una tabla llamada IMPUESTOS con esta estructura:

IMPUESTOS1

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

Y que contiene estos datos:

IMPUESTOS2

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

Una tabla llamada MOVIMIMP (movimientos de impuestos) donde se guardan los datos de todos los impuestos que se deben pagar y que tiene esta estructura:

IMPUESTOS3

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

Y que contiene estos datos:

IMPUESTOS4

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

En el archivo MOVIMCAB (cabecera de movimientos) guardamos la Fecha, el Número del Documento y otros datos. Creamos una vista llamada V_IMPUESTOS para obtener los datos que necesitamos:

CREATE VIEW V_IMPUESTOS(
   MOI_CODSUC,
   MOI_IDECAB,
   MOI_IDEIMP,
   MOI_NOMIMP,
   MOI_MONTOX)
AS
   SELECT
     M.MOI_CODSUC,
      M.MOI_IDECAB,
      M.MOI_IDEIMP,
      I.IMP_NOMBRE AS MOI_NOMIMP,
      M.MOI_MONTOX
   FROM
      MOVIMIMP M
   JOIN
      IMPUESTOS I
         ON M.MOI_CODSUC = I.IMP_CODSUC AND
            M.MOI_IDEIMP = I.IMP_IDENTI;

Y listo, ahora ya podemos utilizar las tablas agregadas. Primero creamos la tabla agregada MISIMPUESTOS1

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'MISIMPUESTOS1',
      'V_IMPUESTOS',
      'MOI_IDECAB INTEGER',
      'MOI_CODSUC INTEGER',
      'MOI_NOMIMP',
      'NUMERIC(17, 4)')

IMPUESTOS5

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

Y luego le insertamos datos a la tabla MISIMPUESTOS1

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'MISIMPUESTOS1',
      'V_IMPUESTOS',
      'MOI_IDECAB INTEGER',
      'MOI_CODSUC INTEGER',
      'MOI_NOMIMP',
      'SUM(MOI_MONTOX)')

IMPUESTOS6

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

Y ya está, eso es todo. Ahora tenemos en la tabla MISIMPUESTOS1 todo lo que necesitamos saber sobre los impuestos. Y lo más importante es que nuestras tablas están normalizadas. Por lo tanto, si el Gobierno decide crear un nuevo impuesto no necesitaremos tocar las estructuras de nuestras tablas ni de nuestras vistas ni de nuestros stored procedures ni de nuestros triggers. Simplemente agregamos el nombre del nuevo impuesto en la tabla IMPUESTOS y el porcentaje del impuesto en la tabla CAMBIOS_IMP y listo, asunto solucionado.

Conclusión:

Gracias a los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT desde ahora ya es demasiado fácil tener tablas agregadas. Lo único que debes hacer es escribir una vista que contenga los datos que pasarán a la tabla agregada y luego llamar a esos stored procedures con los parámetros adecuados, como viste en los ejemplos anteriores. Y listo, eso es todo. Muy fácil y muy sencillamente podrás tener todas las tablas agregadas que necesites.

Artículos relacionados:

Consideraciones a tener en cuenta al diseñar una Base de Datos

Diseño de bases de datos. 1FN

Diseño de bases de datos. 2FN

Diseño de bases de datos. 3FN

Más ejemplos de tablas que no cumplen con la 1FN

El manejo de los impuestos

Creando y actualizando tablas agregadas de uso general

El índice del blog Firebird21

Creando y actualizando tablas agregadas de uso general

1 comentario

En este artículo ya habíamos visto una forma de crear tablas agregadas y de insertarles datos:

https://firebird21.wordpress.com/2013/12/28/creando-tablas-agregadas-e-insertandoles-datos/

sin embargo, tenía un problema: para cada tabla agregada que decidieras crear tendrías que escribir dos stored procedures:

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

Si solamente vas a necesitar una o dos tablas agregadas eso no sería un problema pero si necesitarás 30 ó 40 tablas agregadas entonces ya es otro tema porque tendrías que escribir 60 u 80 stored procedures. Muy aburrido.

Por ese motivo escribí dos stored procedures de uso general. Siempre se necesitan al menos dos porque dentro de un stored procedure no se puede escribir un COMMIT y si no se le hizo el COMMIT a una tabla entonces no se le pueden insertar datos.

También escribí otro stored procedure llamado PARSER cuya misión es extraer un subtexto dentro de un texto.

El stored procedure PARSER


CREATE PROCEDURE PARSER(
      tcTexto     VARCHAR(18192),
      tcSeparador VARCHAR(12))
   RETURNS(
      ftcNombre    VARCHAR(1024))
AS
   DECLARE VARIABLE lnPosicion SMALLINT;
BEGIN

   lnPosicion = Position(tcSeparador IN tcTexto);

   ftcNombre = Left(tcTexto, lnPosicion - 1) ;

END;

Este stored procedure extrae de un texto un subtexto, para saber cual es el subtexto que debe extraer se usa un separador o delimitador. Algunos ejemplos:

PARSER de ‘hoy,es,un,día,soleado’ usando como delimitador la coma nos dará como resultado ‘hoy’

PARSER de ‘mañana*será*otro*día’ usando como delimitador el asterisco nos dará como resultado ‘mañana’

El delimitador puede tener más de un caracter si lo deseas. Está definido VARCHAR(12) así que podrías tener delimitadores de hasta 12 caracteres. Nunca necesitarás tanto, pero bueno, ahí está.

Las vista V_MOVIMIENTOS

Los datos que se insertarán en la tabla agregada deben tener un origen, o sea que deben salir de algún lado. Ese origen puede ser una tabla o una vista, en estos ejemplos usé una vista llamada V_MOVIMIENTOS.

CREATE VIEW V_MOVIMIENTOS(
   MOV_IDECAB,
   MOV_FECHAX,
   MOV_TIPDOC,
   MOV_NRODOC,
   MOV_IDEPRD,
   MOV_NOMPRD,
   MOV_CANTID)
AS
   SELECT
      D.MOV_IDECAB,
      C.MVC_FECHAX AS MOV_FECHAX,
      C.MVC_TIPDOC AS MOV_TIPDOC,
      C.MVC_NRODOC AS MOV_NRODOC,
      D.MOV_IDEPRD,
      P.PRD_NOMBRE AS MOV_NOMPRD,
      D.MOV_CANTID
   FROM
      MOVIMDET D
   JOIN
      MOVIMCAB C
         ON D.MOV_IDECAB = C.MVC_IDENTI
   JOIN
      PRODUCTOS P
         ON D.MOV_IDEPRD = P.PRD_IDENTI;

En la tabla PRODUCTOS tenemos los datos de los productos, en la tabla MOVIMDET (detalles de movimientos) los detalles de las ventas y en la tabla MOVIMCAB (cabecera de movimientos) los datos de cabecera.

PIVOT1

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

El stored procedure CREAR_TABLA_PIVOT

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(28),
   tcVista                  VARCHAR(28),
   tcPrimeraColumnaCabecera VARCHAR(64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(28),
   tcTipoDatos              VARCHAR(64))
AS
   DECLARE VARIABLE lcCreate VARCHAR(4096);
   DECLARE VARIABLE lcColumna VARCHAR(28);
   DECLARE VARIABLE lcMensajeError VARCHAR(1024);
BEGIN

   -- Primero, creamos la tabla

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

   lcCreate = lcCreate || tcPrimeraColumnaCabecera || ' NOT NULL, ';

   IF (Char_Length(tcOtrasColumnasCabecera) > 0) THEN BEGIN
      tcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',';
      WHILE (Char_Length(tcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(tcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcCreate = lcCreate || Trim(lcColumna) || ' , ' ;
         tcOtrasColumnasCabecera = Replace(tcOtrasColumnasCabecera, lcColumna || ',', '');
      END
   END

   FOR EXECUTE STATEMENT
      'SELECT DISTINCT ' || tcColumnaDatos || ' FROM ' || tcVista INTO :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcColumna = Replace(lcColumna, '%', '_');
      lcCreate = lcCreate || lcColumna || ' ' || tcTipoDatos || ', ' ;
   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 (' || Left(:tcPrimeraColumnaCabecera, Position(' ', :tcPrimeraColumnaCabecera)) || ')';

END;

Este stored procedure te permite crear una tabla y es totalmente general, esa tabla puede tener la cantidad de columnas fijas que desees. Puedes crear una tabla con 1 columna fija, otra tabla con 2 columnas fijas, otra tabla con 3 columnas fijas, etc. Las columnas variables son ilimitadas pero debes mantener un número que sea manejable. En estos ejemplos si tienes 4 productos entonces tendrás 4 columnas, si tienes 20 productos entonces tendrás 20 columnas. Pero si tienes 300 productos nadie se molestará en mirar todas las columnas de la tabla, así que sería una pérdida de tiempo crearla.

Mi regla cuando uso tablas agregadas es nunca crearlas con más de 12 columnas variables porque simplemente nadie las utiliza cuando tienen más columnas, así que ni vale la pena crearlas.

El stored procedure ACTUALIZAR_TABLA_PIVOT

CREATE PROCEDURE ACTUALIZAR_TABLA_PIVOT(
   tcTablaPivot             VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcValoresDatos           VARCHAR(1024))
AS
   DECLARE VARIABLE lcOtrasColumnasCabecera VARCHAR(4096);
   DECLARE VARIABLE lcSelect                VARCHAR(4096);
   DECLARE VARIABLE lcColumna               VARCHAR(  28);
   DECLARE VARIABLE lcGrupo                 VARCHAR(4096);
   DECLARE VARIABLE lcInto1                 VARCHAR(1024);
   DECLARE VARIABLE lcInto2                 VARCHAR(1024);
   DECLARE VARIABLE lcInto3                 VARCHAR(1024);
   DECLARE VARIABLE lcActualizar            VARCHAR(1024);
BEGIN

   lcOtrasColumnasCabecera = tcOtrasColumnasCabecera;

   lcSelect = 'SELECT ' ||
              Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ', ' ;

   lcGrupo = Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ', ' ;

   IF (Char_Length(lcOtrasColumnasCabecera) > 0) THEN BEGIN
      lcOtrasColumnasCabecera = lcOtrasColumnasCabecera || ',' ;
      WHILE (Char_Length(lcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(lcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcGrupo = lcGrupo || Left(lcColumna, Position(' ', lcColumna) - 1) || ',' ;
         lcOtrasColumnasCabecera = Replace(lcOtrasColumnasCabecera, lcColumna || ',', '') ;
      END
   END

   lcSelect = lcSelect ||
              tcColumnaDatos || ', ' ||
              tcValoresDatos ||
              ' FROM ' || tcVista ||
              ' GROUP BY ' || lcGrupo ||
              tcColumnaDatos ;

   FOR EXECUTE STATEMENT
      lcSelect
   INTO
      :lcInto1,
      :lcInto2,
      :lcInto3
   DO BEGIN
      -- Aquí se insertan o actualizan la primera columna y los datos agrupados
      lcInto2 = Left(lcInto2, 28);
      lcInto2 = Replace(lcInto2, ' ', '_');
      lcInto2 = Replace(lcInto2, '.', '_');
      lcInto2 = Replace(lcInto2, '/', '_');
      lcInto2 = Replace(lcInto2, '%', '_');
      lcActualizar = 'UPDATE OR INSERT INTO ' ||
                     tcTablaPivot || ' (' ||
                     Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || ',' ||
                     lcInto2 ||
                     ') VALUES(' ||
                     lcInto1 || ',' ||
                     lcInto3 || ')' ;
      EXECUTE STATEMENT lcActualizar;
      IF (Char_Length(Trim(tcOtrasColumnasCabecera)) > 0) THEN BEGIN
         lcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',' ;
         lcActualizar = 'UPDATE ' || tcTablaPivot || ' SET ' ;
         WHILE (Char_Length(lcOtrasColumnasCabecera) > 0) DO BEGIN
            -- Aquí se actualizan todas las demás columnas de la cabecera
            EXECUTE PROCEDURE Parser(lcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
            lcActualizar = lcActualizar ||
                           Left(lcColumna, Position(' ', lcColumna) - 1) ||
                           ' = (SELECT ' || Left(lcColumna, Position(' ', lcColumna) - 1) ||
                           ' FROM ' || tcVista ||
                           ' WHERE ' ||
                           Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) ||
                           '=' || lcInto1 ||
                           ' ROWS 1),' ;
            lcOtrasColumnasCabecera = Replace(lcOtrasColumnasCabecera, lcColumna || ',', '') ;
         END
         lcActualizar = Left(lcActualizar, Char_Length(lcActualizar) - 1) ||
                       ' WHERE ' ||
                       Left(tcPrimeraColumnaCabecera, Position(' ', tcPrimeraColumnaCabecera) - 1) || '=' ||
         lcInto1;
         EXECUTE STATEMENT lcActualizar;
      END
   END

END;

Este stored procedure es el encargado de insertarle datos a la tabla agregada. No importa cuantas columnas tenga la tabla, él se encargará de la inserción y actualización de los datos.

Ejemplo

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT(
      'PRUEBA1',
      'V_MOVIMIENTOS',
      'MOV_IDECAB INTEGER',
      'MOV_FECHAX DATE,MOV_TIPDOC SMALLINT,MOV_NRODOC VARCHAR(15)',
      'MOV_NOMPRD',
      'NUMERIC(17, 4)')

PIVOT3

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

Como puedes ver, la tabla PRUEBA1 se creó exactamente como se le indicó.

EXECUTE PROCEDURE ACTUALIZAR_TABLA_PIVOT(
   'PRUEBA1',
   'V_MOVIMIENTOS',
   'MOV_IDECAB INTEGER',
   'MOV_FECHAX DATE,MOV_TIPDOC SMALLINT,MOV_NRODOC VARCHAR(15)',
   'MOV_NOMPRD',
   'SUM(MOV_CANTID)')

Y este es el stored procedure encargado de insertarle y de actualizarle las filas a la tabla PRUEBA1. Al ejecutarlo esto es lo que obtenemos:

PIVOT2

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

Donde tenemos 4 columnas fijas (MOV_IDECAB, MOV_FECHAX, MOV_TIPDOC, MOV_NRODOC) y 4 columnas variables (todas las demás).

Los parámetros de entrada de los stored procedures

tcNombreTabla. Es el nombre que queremos darle a la tabla que vamos a crear, o el nombre que tiene la tabla a la cual le insertaremos datos. Para saber si la tabla ya existe y no intentar crearla en ese caso podrías escribir:

lcTablaExiste = IIF((SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'PRUEBA1') = 'PRUEBA1', 'S', 'N')

Si el valor de la variable lcTablaExiste es igual a ‘S’ entonces ya existe una tabla con el nombre ‘PRUEBA1’.

tcVista. Es el nombre de la vista o de la tabla que contiene los datos que serán agrupados.

tcPrimeraColumnaCabecera. Es el nombre que veremos en la primera columna. Este dato siempre debe especificarse porque no se puede crear ni usar la tabla si no se lo especifica. Las filas de la vista tcvista son agrupadas según esta columna. Fíjate que debes escribir el nombre de una columna de tcvista y también su tipo de datos (SMALLINT, INTEGER, etc.).

tcOtrasColumnasCabecera. Esto es opcional, no es obligatorio tener más de una columna fija. Si las usarás entonces deberás escribir el nombre de la columna, su tipo de datos (INTEGER, DATE, NUMERIC, etc.) y luego una coma para separar a una columna de la siguiente.

tcColumnaDatos. Es la columna que tiene los datos variables. En nuestro ejemplo, es la columna donde se tienen registrados los nombres de los productos. En otros casos podría tener nombres de alumnos, de profesores, de países, de libros, etc.

tcTipoDatos. Es el tipo de los datos variables. Debe ser uno numérico (SMALLINT, INTEGER, DECIMAL, NUMERIC, etc.) y se lo necesita cuando se crea la tabla.

tcValoresDatos. En general se trata de una función agregada. El resultado de ejecutar la función agregada es el que se guardará en las columnas variables. Ejemplos: SUM(MOV_CANTID), SUM(MOV_CANTID * MOV_PRECIO), MAX(MOV_PRECIO), etc.

Manejo de errores

Para que los stored procedures no sean muy largos no les escribí código que se encargue de atrapar los errores, pero es algo que tú deberías hacer para evitar errores como los siguientes:

  • No existe la tabla
  • No existe el nombre de la columna
  • No existe el tipo de datos
  • El tipo de datos es incorrecto
  • etc.

Conclusión:

Si usamos tablas agregadas nuestras aplicaciones se potenciarán muchísimo porque ellas les permiten a los gerentes analizar lo que está ocurriendo y por lo tanto son de una gran ayuda para la toma de decisiones.

Los stored procedures que has visto en este artículo son de uso general, o sea que te servirán con cualquier tabla y con cualquier vista. Además, puedes tener todas las columnas fijas y todas las columnas variables que desees. Sin embargo, debes recordar que la mayoría de las personas no mirarán tablas que tengan más de 12 columnas variables y eso implica que en tu vista debes poner condiciones que limiten la cantidad de filas con nombres de productos (o de lo que sea) distintos.

Artículos relacionados:

Creando tablas agregadas e insertándoles datos

El índice del blog Firebird21

Creando tablas agregadas e insertándoles datos

3 comentarios

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

Creando tablas dentro de un stored procedure o de un trigger

2 comentarios

Como seguramente sabes, lo normal y generalmente lo mejor es que crees tablas afuera de los stored procedures y de los triggers. ¿Por qué? porque de esa manera es el propio Firebird quien se encargará de verificar que todo esté correcto en tiempo de compilación, o sea cuando los usuarios aún no están usando la Base de Datos. Si él descubre algún error (por ejemplo quisiste escribir INTEGER pero escribiste INTEGGGGER) te mostrará un mensaje adecuado y no te permitirá grabar la estructura de esa tabla. O sea que en tiempo de compilación ya descubrió el error y te lo señaló. Y eso está muy bien.

Sin embargo, a veces puedes necesitar crear tablas cuyas características desconoces en tiempo de compilación y solamente las puedes conocer en tiempo de ejecución (o sea cuando los usuarios ya están usando la Base de Datos). El problema es que si escribiste algo mal recién te enterarás en tiempo de ejecución.

Para crear una tabla en tiempo de ejecución utilizaremos el comando EXECUTE STATEMENT, como vemos a continuación:

CREATE PROCEDURE CREAR_TABLA
AS
   DECLARE VARIABLE lcComando VARCHAR(4096);
BEGIN

   lcComando = 'CREATE TABLE PRUEBA1 (
                   PRU_NUMERO INTEGER,
                   PRU_NOMBRE VARCHAR(40),
                   PRU_CANTID SMALLINT);' ;

   EXECUTE STATEMENT lcComando;

END;

Como puedes ver, el truco está en escribir un comando que se encargue de crear la tabla. Desde luego que el comando debe ser sintáticamente correcto, ya que si hay un error (escribiste INTEGGGER en lugar de INTEGER) la tabla no será creada y verás un mensaje similar al siguiente:

Invalid command.
Specified domain or source column INTEGGGER does not exist.
At procedure ‘CREAR_TABLA’ line: 11, col: 4.

Pero si escribiste todo bien podrás crear todas las tablas que quieras en tiempo de ejecución. Y eso puede ser muy útil en ciertos casos.

Además, no solamente puedes crear tablas, también si lo deseas puedes alterar tablas, borrar tablas, crearles índices, Primary Keys, Foreign Keys, etc.

CREATE PROCEDURE BORRAR_TABLA
AS
   DECLARE VARIABLE lcComando VARCHAR(1024);
BEGIN

   lcComando = 'DROP TABLE PRUEBA1;' ;

   EXECUTE STATEMENT lcComando;

END;

El stored procedure BORRAR_TABLA se encarga de borrar la tabla PRUEBA1, también si quisiéramos podríamos hacerlo más general, para que pueda borrar a cualquier tabla, no solamente a PRUEBA1. En ese caso escribiríamos algo como:

CREATE PROCEDURE BORRAR_TABLA(
   tcNombreTabla VARCHAR(32))
AS
   DECLARE VARIABLE lcComando VARCHAR(1024);
BEGIN

   lcComando = 'DROP TABLE ' || tcNombreTabla || ';' ;

   EXECUTE STATEMENT lcComando;

END;

Y lo ejecutaríamos de esta manera:

EXECUTE PROCEDURE BORRAR_TABLA('PRUEBA1');

Donde el stored procedure recibe como parámetro de entrada el nombre de la tabla que se desea borrar.

Manejo de errores

Si no eres cuidadoso varios errores pueden ocurrir, por ejemplo: quieres crear la tabla PRUEBA1 pero ya existe la tabla PRUEBA1. O quieres borrar la tabla PRUEBA1 pero no existe una tabla con ese nombre. O cuando creas una tabla su estructura está mal. O muchos otros.

Debes estar prevenido ante esas posibilidades y en tu stored procedure o trigger prever todas las posibilidades y usar excepciones para manejar los errores.

Artículos relacionados:

Entendiendo las excepciones

El índice del blog Firebird21

Algunos ejemplos de funciones agregadas

Deja un comentario

En otros artículos ya habíamos visto las funciones agregadas, aquí hay algunos ejemplos más de como usarlas.

SELECT
   SUM(IIF(MOV_CODSUC = 0, MOV_CANTID * MOV_PRECIO, 0)) AS TOTAL0,
   SUM(IIF(MOV_CODSUC = 1, MOV_CANTID * MOV_PRECIO, 0)) AS TOTAL1
FROM
   MOVIMDET

En esta consulta, si el Código de la Sucursal es 0 se realiza la multiplicación y se halla el Total de esa Sucursal. Lo mismo con la Sucursal 1.

SELECT
   COUNT(DISTINCT IIF(MOV_CODSUC = 0, MOV_CANTID, NULL)) AS TOTAL0,
   COUNT(DISTINCT IIF(MOV_CODSUC = 1, MOV_CANTID, NULL)) AS TOTAL1
FROM
   MOVIMDET

En esta consulta contamos cuantas cantidades distintas hay en la Sucursal 0 y en la Sucursal 1 de la tabla MOVIMDET.

SELECT
   COUNT(DISTINCT IIF(MOV_CODSUC = 0, MOV_IDEPRD, NULL)) AS TOTAL0,
   COUNT(DISTINCT IIF(MOV_CODSUC = 1, MOV_IDEPRD, NULL)) AS TOTAL1
FROM
   MOVIMDET

Y en esta consulta contamos cuantos productos distintos hay en la tabla MOVIMDET (la columna MOV_IDEPRD es el Identificador del Producto).

Conclusión:

Lo interesante de los ejemplos es que muestran que dentro de las funciones agregadas se pueden escribir otras funciones.

Artículo relacionado:

El índice del blog Firebird21

 

Older Entries