Enviando una consulta a EXCEL

2 comentarios

Hay muchas formas de enviar el resultado de una consulta a una planilla Excel, aquí veremos una de esas formas.

NOTA: el código fuente está basado en Visual FoxPro pero puede ser fácilmente adaptado a otros lenguajes de programación.

La ventaja de este método es que nos resultará muy fácil enviar la consulta que queremos, al libro Excel que queremos, a la hoja Excel que queremos, en el rango que queremos mostraremos la consulta, y esa consulta puede estar basada en cualquier tabla o en cualquier vista de nuestra Base de Datos del Firebird.

Primero, creamos la función que se encargará de realizar la tarea.

Listado 1.

FUNCTION ENVIAR_EXCEL_CON_DSN
LParameters tcDSN, tcUsuario, tcPassword, tcConsulta, tcRango
Local lcMensajeError, lcConsulta, loExcel, loLibro, loHoja, loTabla, lcConexion, lcBaseDatos

  lcMensajeError = ""

  tcRango = iif(VarType(tcRango) <> "C", "A1", tcRango)    && Si no se definió donde colocar la tabla, se la coloca en la celda A1

  *-- Tratamos de crear un objeto Excel
  if Empty(lcMensajeError)
    loExcel = CreateObject("Excel.Application")
    lcMensajeError = iif(VarType(loExcel) <> "O", "No se pudo crear el objeto EXCEL, verifica si está instaldo EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear un libro Excel
  if Empty(lcMensajeError)
    loLibro = loExcel.Workbooks.Add
    lcMensajeError = iif(VarType(loLibro) <> "O", "No se pudo crear el libro de EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear una hoja en el libro Excel
  if Empty(lcMensajeError)
    loHoja = loLibro.Worksheets(1)
    lcMensajeError = iif(VarType(loHoja) <> "O", "No se pudo crear la hoja en el libro EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, creamos el string de conexión a la Base de Datos del Firebird, usando el DSN de esa Base de Datos
  if Empty(lcMensajeError)
    lcConexion = "ODBC;DSN=" + tcDSN + ";USER=" + tcUsuario + ";PASSWORD=" + tcPassword
  endif

  *-- Si todo OK, tratamos de conectarnos a la Base de Datos del Firebird
  if Empty(lcMensajeError)
    loTabla = loHoja.QueryTables.Add(lcConexion, loHoja.Range(tcRango), tcConsulta)
    lcMensajeError = iif(VarType(loTabla) <> "O", "No se pudo crear la tabla de consulta en la planilla Excel", lcMensajeError)
  endif

  *-- Si todo OK, insertamos la tabla en la planilal Excel
  if Empty(lcMensajeError)
    #DEFINE xlInsertEntireRows 2
    loTabla.RefreshStyle = xlInsertEntireRows
    loTabla.Refresh
  endif

  *-- Si todoOK, mostramos la planilla Excel
  if Empty(lcMensajeError)
    loExcel.Visible = .T.
  endif

  *-- Liberamos de la memoria los objetos que habíamos creado
  loTabla = .NULL.
  loHoja  = .NULL.
  loLibro = .NULL.
  loExcel = .NULL.

Return(lcMensajeError)
*
*

Y después llamamos a esa función, con los parámetros que deseemos usar.

Listado 2.

Local lcDSN, lcUsuario, lcPassword, lcConsulta, lcMensajeError, lcRango

  lcDSN = "AUTOMUNDO-FIREBIRD"

  lcUsuario = "SYSDBA"
  lcPassword = "masterkey"

   lcConsulta = "SELECT " ;
              + "   ASC_ANOEJE, " ;
              + "   ASC_CODSUC, " ;
              + "   ASC_FECHAX, " ;
              + "   ASC_NUMERO " ;
              + "FROM " ;
              + "   V_CONTA_ABM_ASIENTOS " ;
              + "WHERE " ;
              + "   ASC_NUMERO >= 100 AND " ;
              + "   ASC_NUMERO <= 120"

  lcRango = "B5"

  lcMensajeError = ENVIAR_EXCEL_CON_DSN(lcDSN, lcUsuario, lcPassword, lcConsulta, lcRango)

  *-- Si ocurrió algún error, mostramos un mensaje al usuario
  if !Empty(lcMensajeError)
    #DEFINE MSG_ICONO_ERROR 16
    #DEFINE MSG_BOTON_ACEPTAR 0
    =MessageBox(lcMensajeError, MSG_ICONO_ERROR + MSG_BOTON_ACEPTAR, "Hay un problema...")
  endif

Return
*
*

Y así obtendremos una planilla similar a la siguiente:

EXCEL01

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

Por lo tanto, nos resultará muy sencillo crear una planilla Excel con los datos que el usuario pueda necesitar. Inclusive podríamos tener en nuestra aplicación una opción que le muestre todas las vistas disponibles y que le permita crear consultas personalizadas, a su gusto y paladar. Ya depende de la creatividad de cada uno hacer algo así.

Artículos relacionados:

Usando EXCEL para leer bases de datos de Firebird

Planillas EXCEL dinámicas con Firebird (1)

Planillas EXCEL dinámicas con Firebird (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

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

3 comentarios

Así como podemos consultar filas de forma recursiva, también podemos actualizar esas filas recursivamente si es lo que necesitamos.

Veamos un caso:

Tenemos una tabla llamada CUENTAS que tiene estos datos:

UPDATE1

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

En la columna CUE_IDENTI guardamos el identificador de cada fila; en la columna CUE_IDEPAD guardamos el identificador de su fila padre; en la columna CUE_TIMEST guardamos la fecha y la hora de la última actualización de una cuenta. Ahora supongamos que queremos que la cuenta CRÉDITOS y todas las cuentas que sean sus descendientes tengan la fecha y la hora actuales.

Listado 1.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (
      WITH RECURSIVE MiCTE AS (
         
         SELECT
            CUE_IDENTI
         FROM
            CUENTAS
         WHERE
            CUE_IDENTI = 6
         
         UNION ALL
         
         SELECT
            T1.CUE_IDENTI
         FROM
            CUENTAS T1
         JOIN
            MiCTE 
               ON T1.CUE_IDEPAD = MiCTE.CUE_IDENTI
         
      )
      
      SELECT CUE_IDENTI FROM MiCTE

   );

UPDATE2

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

Tal y como podemos ver en la Captura 2. se actualizó la columna CUE_TIMEST de la cuenta que tiene CUE_IDENTI =6 y también de todos sus descendientes. En el Listado 1. podemos ver que la primera cuenta a ser actualizada es la que tiene CUE_IDENTI = 6, a continuación se actualizan las demás filas.

Desde luego que no solamente podemos cambiar la fecha y la hora al actualizar, podemos cambiar cualquier dato que necesitemos.

Explicación:

Como en la cláusula WHERE del UPDATE estamos usando IN eso significa que queremos actualizar varias filas. ¿Qué filas actualizaremos? Aquellas cuya columna CUE_IDENTI se encuentre en la tabla virtual que crearemos. En nuestra tabla virtual la primera fila es la que tiene CUE_IDENTI = 6, así que esa fila sí o sí será actualizada. A continuación hallamos el CUE_IDENTI de las demás filas que queremos actualizar. Como la tabla virtual es recursiva entonces se le irán agregando filas hasta que la condición del JOIN deje de cumplirse. El último SELECT es el que devuelve todas las filas de la tabla virtual, y todas esas filas tienen una sola columna, llamada CUE_IDENTI.

Entonces, nuestro UPDATE recursivo sería el equivalente a este UPDATE no recursivo.

Listado 2.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (6, 7, 8, 9, 10)

¿Cuál es la diferencia? Que en el Listado 2. debemos conocer  todos los identificadores que deseamos actualizar, en cambio en el Listado 1., no. Allí solamente necesitamos conocer el identificador de la primera cuenta, nada más. Todas las cuentas que sean sus descendientes serán actualizadas, sean 4 como en este caso o sean 500 o sean 1000. La única limitación es que pueden ser como máximo 1024 porque el Firebird permite hasta 1024 recursiones.

El Listado 2. es más sencillo, pero requiere conocer los identificadores de todas las cuentas que queremos actualizar y si los usuarios están constantemente agregando cuentas tendremos que estar cambiando nuestro UPDATE a cada rato. Por el contrario con el Listado 1. lo único que debemos conocer es el identificador de la primera cuenta que deseamos actualizar y todas las cuentas que sean sus descendientes se actualizarán también.

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

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo las tablas autoreferenciadas

1 comentario

Como ya hemos visto en estos dos artículos:

https://firebird21.wordpress.com/2013/05/06/ejemplo-no-004-usando-un-inner-join-para-autoreferenciar-una-tabla/

https://firebird21.wordpress.com/2013/07/19/autorreferenciar-una-tabla-algunos-ejemplos/

podemos escribir una consulta en la cual una tabla se referencia a sí misma. Explayémonos un poco más sobre este tema.

Evidentemente para que referenciemos una tabla con sí misma en ella deben encontrarse los datos que necesitamos. Aquí tenemos dos posibilidades:

  1. Que la referencia se haga a dos columnas de la tabla
  2. Que la referencia se haga a una sola columna de la tabla

Quizás habrás notado que en el primero de los dos artículos superiores se usaron dos columnas y en el segundo artículo solamente se usó una columna.

En general, cuando se trata de una tabla maestra (es decir: una tabla que no depende de otras tablas) usamos la primera opción. Y cuando se trata de una tabla de movimientos (es decir: una tabla que depende de otras tablas) usamos la segunda opción. Pero esa no es una regla inmutable, es lo más común pero no está grabado en piedra.

Ejemplo 1:

Necesitamos registrar los datos de algunos animales (por ejemplo: perros) y entre otras cosas queremos saber cual es el padre de cada perro, cual es el abuelo, etc. (lo que en castellano se llama pedigrí)

Para ello podríamos tener una tabla para los hijos, otra para los padres, otra para los abuelos, etc. Pero sería tedioso trabajar así porque tendríamos muchos datos duplicados (un mismo perro podría encontrarse en dos o en tres tablas). Una manera mucho más racional sería tener una sola tabla e identificar en ella al padre de cada perro.

AUTO6

(si haces clic en la imagen la verás más grande)

Y los datos de esa tabla podrían ser algo así:

AUTO7

(si haces clic en la imagen la verás más grande)

Entonces, para ver los nombres de cada perro y los nombres de sus respectivos padres podríamos escribir:

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI

Como ves, la tabla ANIMALES tiene un JOIN a la tabla ANIMALES, o sea que se referencia a sí misma. Al ejecutar ese SELECT obtendríamos este resultado:

AUTO8

(si haces clic en la imagen la verás más grande)

Donde podemos ver el nombre del padre de cada perro. En algunos casos, también podríamos obtener el nombre del abuelo (INDIO es hijo de AFRO quien a su vez es hijo de SATAN).

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre",
   A.ANI_IDENTI AS "Id. abuelo",
   A.ANI_NOMBRE AS "Nombre del abuelo"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI
LEFT JOIN
   ANIMALES A
      ON P.ANI_IDEPAD = A.ANI_IDENTI

Si ejecutamos este SELECT esto será lo que obtendremos:

AUTO9

(si haces clic en la imagen la verás más grande)

 El cual claramente nos indica que el padre de INDIO es AFRO y que su abuelo es SATAN. De similar manera podríamos obtener los nombres de los perros que no tienen padre (bueno, en la vida real sí lo tienen pero no los tenemos registrados quizás porque no sabemos sus nombres), de los perros que no tienen abuelos, de los perros que son hermanos entre sí, de los hijos de un perro, de los nietos de un perro, etc.

En este ejemplo hemos usado dos columnas de la tabla ANIMALES (ANI_IDENTI y ANI_IDEPAD) para hacer la referencia.

Ejemplo 2:

Queremos conocer los nombres de los alumnos que se han aplazado en Matemática y también se han aplazado en Historia. Las calificaciones van del 0 al 100 y quienes obtienen menos de 40 puntos se aplazan.

La estructura de nuestra tabla de CALIFICACIONES es la siguiente:

AUTO10

(si haces clic en la imagen la verás más grande)

Y estas son algunas de sus filas:

AUTO11

(si haces clic en la imagen la verás más grande)

 Entonces, para ver los nombres de los alumnos que se aplazaron en Matemática y también en Historia podríamos escribir:

SELECT
   C.CAL_IDEALU AS "Id. alumno",
   A.ALU_NOMBRE AS "Nombre del alumno",
   C.CAL_NOTAXX AS "Matemática",
   D.CAL_NOTAXX AS "Historia"
FROM
   CALIFICACIONES C
JOIN
   CALIFICACIONES D
      ON C.CAL_IDEALU = D.CAL_IDEALU
JOIN
   ALUMNOS A
      ON C.CAL_IDEALU = A.ALU_IDENTI
WHERE
   C.CAL_NOTAXX < 40 AND
   D.CAL_NOTAXX < 40 AND
   C.CAL_CODMAT = 'MAT' AND
   D.CAL_CODMAT = 'HIS'

Con lo cual obtendríamos:

AUTO12

(si haces clic en la imagen la verás más grande)

 Y así sabríamos que RAQUEL se ha aplazado en ambas materias.

En este ejemplo hemos usado solamente una columna de la tabla CALIFICACIONES (la columna: CAL_IDEALU) para hacer la referencia pero hemos necesitado además de un WHERE para establecer la condición.

Conclusión:

Hay muchos casos en los cuales lo más eficiente que podemos tener es una tabla que se referencia a sí misma. Siempre que podamos meter toda la información que necesitamos en una sola tabla es una posibilidad a considerar, pues evitamos la redundancia y en general las consultas son más rápidas.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autorreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

Consultando con peso de las variables buscadas

1 comentario

A veces podemos necesitar realizar una consulta en la cual los resultados se muestren ordenados por la cantidad de ocurrencias de las variables buscadas.

Ejemplo:

Tenemos una tabla de PROVEEDORES que tiene las columnas Código del País, Código del Departamento (Estado o Provincia), Código de la Localidad.

Y lo que deseamos es que en la consulta se muestren en primer lugar los proveedores de la localidad especificada, luego los del departamento especificado y finalmente los del país especificado.

¿Por qué?

Porque debemos hacer un pedido de compras y necesitamos que la mercadería llegue lo más pronto posible, por lo tanto cuanto más cerca de nosotros está el proveedor es más probable que la mercadería llegue más rápido (si está a 2 km se supone que llegará más rápido que si está a 300 km).

Para ello, si una columna cumple con la condición le asignamos un número que es potencia de 2. Y si no la cumple, le asignamos el número 0. A la suma de todos esos valores le llamamos Peso.

Y en la consulta, mostramos primero los los pesos mayores.

SELECT
   (IIF(PRO_PAISXX =  595, 4, 0) +          -- Código del país
    IIF(PRO_CODDEP = '00', 2, 0) +          -- Código del departamento
    IIF(PRO_CODLOC = '01', 1, 0)) AS PESO,  -- Código de la localidad
   PRO_NOMBRE,
   PRO_TELEFO,
   PRO_EMAILX
FROM
   PROVEEDORES
ORDER BY
   1 DESCENDING

Como hay 3 columnas que nos interesan los valores sumados fueron 4, 2, 1. Si hubieran sido 4 columnas entonces usaríamos 8, 4, 2, 1. Con 5 columnas sería 16, 8, 4, 2, 1. Y así sucesivamente.

Como puedes ver, a algunas columnas se les dió más peso que a otras. En este ejemplo la más importante es el País, luego le sigue el Departamento y finalmente la Localidad. En otros casos podría ocurrir que todas las columnas tengan el mismo peso y entonces podrías asignarle un valor de 1 a cada una de ellas.

Por supuesto que esta misma técnica puedes usar con columnas de tipo carácter y las cláusulas LIKE, STARTING WITH, etc.

Artículos relacionados

El índice del blog Firebird21

Una forma rápida de escribir un SELECT

1 comentario

A veces, queremos escribir un SELECT el cual tendrá todas o casi todas las columnas de una tabla. Si queremos todas las columnas podríamos usar el asterisco * pero esa es una práctica no recomendada.

Si usamos el programa EMS SQL Manager tenemos una alternativa interesante y que nos facilitará el trabajo: hacemos clic con el botón derecho sobre el nombre de la tabla, luego elegimos la opción “Script to New SQL Editor” y luego la opción “Select”, como se ve en esta imagen:

SELECT1(haciendo clic en la imagen la verás más grande)

Y esto es lo que obtenemos:

SELECT2

(haciendo clic en la imagen la verás más grande)

O sea, un SELECT con todas las columnas de la tabla. Esto puede ahorrarnos mucho tiempo, sobre todo con tablas que tienen decenas de columnas.  Este SELECT podemos ahora “copiarlo y pegarlo” donde lo necesitemos.