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

 

Anuncios

Planillas EXCEL dinámicas con Firebird (1)

Deja un comentario

Si queremos darle un muy alto valor agregado a nuestras aplicaciones entonces podemos proveerles de planillas Excel dinámicas. A nuestros clientes les encantará eso.

¿Qué es una planilla Excel dinámica?

Una planilla cuyos datos no fueron introducidos a mano sino que provienen de una fuente externa, en nuestro caso de una Base de Datos de Firebird.

Pero lo interesante aquí es que si cambian los datos de nuestra Base de Datos, también cambia la planilla Excel, automáticamente. Y si cambiamos el valor de algunas celdas en nuestra planilla Excel, entonces obtendremos una planilla Excel actualizada.

Muy interesante ¿verdad?

En este primer artículo de la serie veremos como obtener datos de las tablas y de las vistas que tenemos en nuestra Base de Datos de Firebird. En el siguiente artículo veremos algo quizás más interesante: cambiar el valor de una celda en la planilla Excel y que la planilla se actualice automáticamente.

EXCEL01

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

El Diagrama 1. nos muestra el proceso.

  1. Es nuestra Base de Datos creada con Firebird
  2. Usamos OLEDB u ODBC para enviar los datos
  3. Un archivo de conexión define  toda la información que se necesita para acceder y recuperar los datos de nuestra Base de Datos
  4. La información de la conexión es copiada desde el archivo de conexión hacia la planilla Excel
  5. Los datos son copiados dentro de la planilla Excel y ya pueden ser usados como si hubieran sido introducidos manualmente

Usando ODBC para la conexión

  1. El driver ODBC de Firebird debe estar instalado en la computadora donde se encuentra la Base de Datos
  2. Hay que definir un DSN (Data Source Name) para que la conexión pueda realizarse: Inicio | Panel de Control | Herramientas administrativas | Orígenes de datos ODBC | DSN de usuario | Agregar … | Firebird/Interbase driver | completar los datos pedidos

EXCEL02

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

Para verificar que la conexión puede realizarse exitosamente es aconsejable hacer clic sobre el botón “Probar conexión”. Si todo está ok veremos algo así:

EXCEL03

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

Conectando la planilla Excel a la Base de Datos de Firebird

  1. Abrir Excel
  2. Crear una nueva planilla
  3. Datos | De otras fuentes | Desde Microsoft Query | elegimos el DSN de nuestra Base de Datos

EXCEL04

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

EXCEL05

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

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

Como podemos ver en la Captura 5., el Excel nos muestra los nombres de todas las tablas y de todas las vistas que se encuentran en nuestra Base de Datos. Para ver solamente tablas o solamente vistas, podemos hacer clic en Opciones, como vemos a continuación:

EXCEL19

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

y si hacemos clic sobre el signo + que se encuentra a la izquierda de las tablas y de las vistas, veremos los nombres de las columnas pertenecientes a la tabla o a la vista elegida.

EXCEL07

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

Para elegir cuales de esas columnas queremos ver en nuestra planilla Excel, la seleccionamos y luego hacemos clic en el botón que la trasladará a “Columnas en la consulta:”

EXCEL08

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

A continuación podemos Filtrar los datos y elegir el Criterio de ordenación y pedirle que muestre los datos en una planilla Excel.

EXCEL09

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

EXCEL10

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

Y ahora viene lo más interesante de todo, si elegimos “Informe de tabla dinámica” o si elegimos “Informe de gráfico y tabla dinámicos” entonces cada vez que se cambien los datos de nuestra Base de Datos también se cambiarán en la planilla Excel.

Algunos puntos a considerar

a) Los datos en la tabla dinámica están resumidos, o agrupados por filas. Varias filas de los datos originales ocuparán una sola fila en la planilla Excel

EXCEL11

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

EXCEL12

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

En la Captura 11. podemos ver los datos originales que tenemos en la tabla de nuestra Base de Datos. En la Captura 12. vemos la planilla Excel que fue creada a partir de esos datos. Como puedes ver, las filas se agruparon por fechas, y en las columnas numéricas se muestran las sumas de dichas columnas. Eso muchas veces no tiene sentido (como sumar los años en la columna ASC_ANOEJE, año del ejercicio contable, por ejemplo) pero es lo que hace Excel.

Para ver las filas originales, no agrupadas, podemos hacer clic sobre una de las filas de la planilla, clic con el botón derecho, y luego elegir la opción “Mostrar detalles”.

EXCEL13

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

En este caso, como el cursor está sobre la fila que tiene fecha 03/01/2014, las filas de esa fecha que tenemos en nuestra tabla original serán las mostradas.

 EXCEL14

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

b) Los resultados se crean en una nueva hoja. Un detalle a notar, es que el resultado de esa consulta fue creado en una nueva hoja de la planilla, no en la hoja que estábamos usando.

c) Se puede ver el detalle de todas las filas importadas. Para conseguirlo, arrastramos los nombres de las columnas que se encuentran en “Etiquetas de fila” hacia “Etiquetas de columna”.

EXCEL17

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

EXCEL18

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

y luego continuamos igual que en la Captura 13.

Actualizando la planilla Excel

Por defecto, el contenido de la tabla dinámica que creamos en Excel no se actualiza cuando cambian los datos en el origen (o sea, en nuestra Base de Datos del Firebird)

Si el contenido de nuestra tabla en la Base de Datos de Firebird cambió, podemos mostrar los datos actualizados en la planilla Excel. Tenemos tres opciones:

a) Actualización manual. Colocamos el mouse en cualquier celda de la tabla que insertamos y luego clic con el botón derecho y elegimos la opción Actualizar

EXCEL15

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

Si después de hacer clic en Actualizar volvemos a pedirle que nos muestre los detalles, como ya vimos en la Captura 13., entonces nuestra planilla Excel mostrará los datos actualizados.

EXCEL16

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

EXCEL23

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

También se puede actualizar manualmente la planilla Excel haciendo clic sobre cualquier celda de la tabla dinámica, luego en Opciones | Actualizar | Actualizar o Actualizar todo

b) Actualización automática. También podemos conseguir que la planilla Excel se actualice automáticamente, para ello hacemos clic en cualquier celda de la tabla dinámica creada, luego Opciones | Cambiar origen de datos | Propiedades de conexión … | elegimos el tiempo deseado

EXCEL20

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

En la Captura 20. se pide que la planilla Excel sea actualizada cada 1 minuto, desde luego que tú puedes elegir otro tiempo, esto es sólo un ejemplo.

IMPORTANTE: La hoja creada con “Mostrar detalles”, tal como vimos en la Captura 13. no cambiará, solamente cambia la hoja original.

EXCEL21

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

EXCEL22

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

En la Captura 21. vemos el contenido original de la tabla, se cambió un valor en la columna ASC_COTIZA en la Base de Datos del Firebird y un minuto después vemos que la planilla Excel se actualizó automáticamente, tal como podemos observar en la Captura 22.

c) Actualizar al abrir la planilla Excel. Si queremos que cada vez que se abra la planilla Excel muestre a sus datos actualizados, debemos marcar la casilla “Actualizar al abrir el archivo” que se muestra en la Captura 20.

Conclusión:

Poder enviar el resultado de consultar a nuestras tablas y a nuestras vistas de una Base de Datos del Firebird a una planilla Excel les resultará muy útil a nuestros clientes. Las tablas dinámicas muestran los datos agrupados y permiten que esos datos se actualicen cuando la tabla subyacente del Firebird se actualice. Eso es muy bueno para poder tomar buenas decisiones empresariales.

Hay muchísimo más que aprender sobre las tablas dinámicas de Excel. Es muy aconsejable que busques información en Internet sobre ellas, te resultará muy útil para que tus aplicaciones se destaquen sobre la competencia.

Artículos relacionados:

Tablas dinámicas en Excel

El índice del blog Firebird21

El foro del blog Firebird21

Creando una subconsulta que devuelva muchas columnas

6 comentarios

A veces necesitamos tener en un SELECT varias columnas que provienen de la misma subconsulta. Hay dos formas de conseguir algo así: la forma mala y la forma buena. He visto que muchas personas usan la “forma mala”, así que ahora explicaré la “forma buena”.

Veamos el caso:

Listado 1. La “forma mala”

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiSubColumna1 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna3,
   (SELECT MiSubColumna2 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna4,
   (SELECT MiSubColumna3 FROM MiTabla2 JOIN MiEnlace WHERE MiCondición) AS MiColumna5
FROM
   MiTabla1

Si te fijas en las subconsultas del Listado 1. verás que son muy, muy, similares, la única diferencia es la columna que devuelven a la consulta principal, el resto es idéntico.

¿Cuál es el defecto de hacerlo así?

Que la subconsulta se ejecuta muchas veces (3 veces en el Listado 1., pero en otros casos podría ser más) y eso puede llegar a ser muy lento en tablas grandes.

Además, si la consulta es muy larga, deberás escribir mucho y te complicará la lectura.

Así que veamos una forma alternativa (y mejor) de obtener el mismo resultado:

Listado 2. La “forma buena”

WITH MiSubConsulta AS (
   SELECT
      MiSubColumna1,
      MiSubColumna2,
      MiSubColumna3
   FROM
      MiTabla2
   JOIN
      MiEnlace
   WHERE
      MiCondición
)

SELECT
   T1.MiColumna1,
   T1.MiColumna2,
   T2.MiSubColumna1 AS MiColumna3,
   T2.MiSubColumna2 AS MiColumna4,
   T2.MiSubColumna3 AS MiColumna5
FROM
   MiTabla1      T1
LEFT JOIN
   MiSubConsulta T2
      ON T1.MiColumna1 = T2.MiSubColumna1

¿Cuáles son las ventajas de hacerlo así?

  1. La tabla virtual CTE (es virtual porque solamente existe en la memoria de la computadora) es creada una sola vez y sus columnas pueden ser usadas muchísimas veces. En este ejemplo sus columnas (llamadas MiSubColumna1, MiSubColumna2, y MiSubColumna3) fueron usadas 3 veces pero en otros casos podrían usarse muchas más veces. Como se la crea una sola vez eso es mucho más rápido que crearla 3 veces que era el caso en el Listado 1.
  2. Es muy fácil de entender. Si miramos a la subconsulta muy fácilmente entenderemos lo que hace.

¿Qué fue lo que hicimos?

  1. Creamos una tabla virtual llamada “MiSubConsulta” (el nombre puede ser cualquiera, “MiSubConsulta” es solamente un ejemplo)
  2. En la tabla virtual colocamos todas las columnas que nos interesan
  3. En el SELECT principal hicimos un LEFT JOIN a la tabla virtual y por eso ya pudimos acceder a todas sus columnas. Si la condición de enlace no se cumple entonces tendremos NULL en las columnas que provienen de la tabla virtual.

Observación: La condición de enlace del Listado 2. (T1.MiColumna1 = T2.MiSubColumna1) es solamente un ejemplo, allí tú pondrás la condición de enlace adecuada a tu caso.

Conclusión:

Si necesitamos tener varias subconsultas en la lista de columnas de un SELECT y esas subconsultas son muy similares lo mejor es crear una tabla CTE (o sea, una tabla virtual) y así conseguiremos una mejor velocidad de respuesta y también que nuestro código sea más fácil de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Los alias de las columnas

3 comentarios

En Firebird tenemos la posibilidad de darles nombres alternativos, normalmente conocidos como “alias”, a las columnas de un SELECT pero ¿dónde podemos usar esos alias?

SELECT
   MiColumna1 AS UnAlias,
   MiColumna2 AS OtroAlias
FROM
   MiTabla

Según el estándar SQL (que Firebird cumple) los alias se usan para darle títulos a las columnas. Entonces ¿en qué momento Firebird conoce cuál es el alias de una columna y puede usarlo?

Solamente después de que conoce el valor de todas las columnas, nunca antes. Por lo tanto un alias no puede ser usado en otra columna, ni en la cláusula WHERE ni en la cláusula JOIN pero sí en las cláusulas GROUP BY y ORDER BY.

Ejemplo 1. No se puede usar ALIAS aquí.

Listado 1.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO,
   TOTAL_PRODUCTO / 11 AS TOTAL_IMPUESTO
FROM
   MOVIMDET

En el Listado 1. tenemos un alias llamado TOTAL_PRODUCTO y luego quisimos usarlo en la siguiente columna. Eso no está permitido, fallará.

Ejemplo 2. No se puede usar ALIAS aquí

Listado 2.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
WHERE
   TOTAL_PRODUCTO >= 100000

En el Listado 2. tenemos un alias llamado TOTAL_PRODUCTO y luego quisimos usarlo en la cláusula WHERE. Tampoco está permitido y fallará.

Ejemplo 3. No se puede usar ALIAS aquí

Listado 3.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MVC_TOTALX > TOTAL_PRODUCTO * 2

En el Listado 3. tenemos un alias llamado TOTAL_PRODUCTO y quisimos usarlo en un JOIN. Eso no está permitido y fallará.

Ejemplo 4. Sí se puede usar ALIAS aquí.

Listado 4.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
GROUP BY
   TOTAL_PRODUCTO

En el Listado 4. tenemos un alias llamado TOTAL_PRODUCTO y queremos usarlo en la cláusula GROUP BY. Eso sí está permitido porque los nombres de todas las columnas ya están definidos.

Ejemplo 5. Sí se puede usar ALIAS aquí

Listado 5.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
ORDER BY
   TOTAL_PRODUCTO

En el Listado 5. tenemos un alias llamado TOTAL_PRODUCTO y queremos usarlo en la cláusula ORDER BY. Eso sí está permitido porque los nombres de todas las columnas ya están definidos.

Conclusión:

Solamente se puede usar un alias previamente definido después que todas las columna del SELECT han sido evaluadas, nunca antes. Por ese motivo no se puede usar usar dentro de la lista de columnas ni en la cláusula WHERE ni en la cláusula JOIN pero sí puede usarse en la cláusula GROUP BY y en la cláusula ORDER BY.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

El problema con las consultas negativas

Deja un comentario

Cuando debemos responder una consulta negativa podemos equivocarnos y mostrar resultados erróneos, hay que prestarle mucha atención a esos casos.

¿Cuándo una consulta es negativa?

Cuando en ella usamos el operador NOT o el operador <>

Veamos un ejemplo:

Tenemos una tabla de VENDEDORES y una tabla de VENTAS, en la cual registramos entre otras cosas el Identificador del vendedor que realizó la venta.

PROBLEMA1

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

PROBLEMA2

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

En la columna VEN_IDEVEN se guarda el Identificador del vendedor que realizó la venta.

Consulta positiva. ¿Cuáles vendedores vendieron el día 2 de enero de 2015?

Como esta es una consulta positiva (o sea que no se usa NOT ni <> en ella) podríamos simplemente responderla así:

SELECT
   VEN_IDEVEN
FROM
   VENTAS
WHERE
   VEN_FECHAX = '2015/01/02'

PROBLEMA3

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

O mejor aún, para que no se repitan los identificadores de los vendedores que realizaron más de una venta, de esta manera:

SELECT
   DISTINCT
   VEN_IDEVEN
FROM
   VENTAS
WHERE
   VEN_FECHAX = '2015/01/02'

PROBLEMA4

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

Entonces, el Identificador de cada vendedor aparecerá una sola vez.

Todo bien hasta aquí.

Consulta negativa. ¿Cuáles vendedores no vendieron el día 2 de enero de 2015?

Es muy tentador pensar que esta consulta se responderá si reemplazamos el símbolo de = por el símbolo de <> en el SELECT anterior. Con eso tendremos la respuesta buscada, ¿verdad?

Falso.

Si escribimos esta consulta:

SELECT
   DISTINCT
   VEN_IDEVEN
FROM
   VENTAS
WHERE
   VEN_FECHAX <> '2015/01/02'

Donde lo único que hemos hecho fue cambiar el símbolo = por el símbolo <> lo que obtendremos será esto:

PROBLEMA5

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

La cual, como podemos ver no es la respuesta correcta.

¿Por qué no es la respuesta correcta?

Porque el vendedor cuyo Identificador es 1 (SERGIO) no es mostrado. Y SERGIO tampoco ha vendido, así que debería mostrarse su Identificador.

¿Cuál es la razón de nuestro error?

Que estamos usando la tabla equivocada para responder la consulta. En la tabla VENTAS se guardan los identificadores de los vendedores que realizaron ventas, por lo tanto responder consultas positivas es muy sencillo. Pero en esa tabla no se guardan los identificadores de los vendedores que no realizaron ventas y eso implica que no puede usarse para responder consultas negativas.

Además, si has entendido bien lo que sucede en estos casos habrás notado que para responder una consulta positiva si se encuentra una fila, aunque sea una sola fila, que cumpla la condición ya aparecerá en el conjunto resultado del SELECT, pero para las consultas negativas hay que recorrer toda la tabla y aún así no es seguro que siempre se obtenga la respuesta correcta.

¿Cuál es la solución?

Usar como tabla principal la tabla de VENDEDORES, no la tabla de VENTAS, y filtrar por los vendedores que no han vendido en esa fecha. Entonces tendríamos algo como:

SELECT
   T1.VEN_IDENTI
FROM
   VENDEDORES T1
WHERE
   NOT EXISTS(SELECT 
                 T2.VEN_IDEVEN 
              FROM 
                 VENTAS T2 
              WHERE 
                 T2.VEN_IDEVEN = T1.VEN_IDENTI AND 
                 T2.VEN_FECHAX = '2015/01/02')

PROBLEMA6

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

Aquí sí hemos obtenido la respuesta correcta, porque al usar la función EXISTS() podemos saber si un vendedor ha realizado alguna venta en la fecha 2 de enero de 2015.

Conclusión:

Si en nuestro SELECT usamos el operador NOT o el operador <> debemos prestarle mucha atención al resultado obtenido porque muchas veces no es el que deseamos obtener.

Es bastante frecuente consultar a la tabla equivocada porque se tiene el preconcepto de que si una consulta positiva da un cierto resultado, entonces al cambiarla por una consulta negativa se tendrá el resultado inverso y eso no siempre es cierto.

En general, las consultas negativas se responden usando la función [NOT] EXISTS() o el operador [NOT] IN. Si no usas ninguno de ellos es muy probable que tu consulta negativa sea errónea. Cuidado con ese punto.

Artículos relacionados:

Consultando datos que NO EXISTEN en una tabla

El índice del blog Firebird21

El foro del blog Firebird21

Usando una subconsulta en un JOIN

7 comentarios

Cuando escribimos un SELECT a veces necesitamos usar un JOIN para relacionar a un conjunto de resultados con otro conjunto de resultados. En general tiene esta forma:

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición

Lo que mucha gente no sabe es que en el JOIN no es imprescindible que especifiquemos una tabla, también podemos especificar un SELECT si lo necesitamos. Y nos quedaría de esta forma:

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   (SELECT
       MiColumna4,
       MiColumna5,
       MiColumna6
    FROM
       MiTabla2
    WHERE
       MiCondición1) AS MiAlias
   ON MiCondición2

¿Qué podemos notar acá?

  1. Que la subconsulta (o sea, el segundo SELECT ) se encuentra rodeada por paréntesis
  2. Que la subconsulta tiene un alias (que se encuentra después del AS y puede ser cualquiera, “MiAlias” es sólo un ejemplo)
  3. Que en “MiCondición2” debemos usar ese alias

Ejemplo 1:

SELECT
   M.MVC_FECHAX,
   M.MVC_TIPDOC,
   M.MVC_NRODOC,
   M.MVC_IDECLI,
   MJ.CLI_NOMBRE
FROM
   MOVIMCAB M
JOIN
   (SELECT
       CLI_IDENTI,
       CLI_NOMBRE
    FROM
       CLIENTES) AS MJ
   ON M.MVC_IDECLI = MJ.CLI_IDENTI

Este ejemplo es muy sencillo, y solamente sirve para ilustrar el concepto. No es necesario usar una subconsulta para algo tan simple pero está ahí para que se entienda como funciona.

Como puedes ver, la subconsulta tiene una alias (en este caso ese alias se llama MJ) y es ese alias el que usamos en la condición que hace la relación (o sea, lo que escribimos después del ON).

Ejemplo 2:

Ahora queremos saber los siguientes datos para cada venta realizada:

  1. La fecha de la venta
  2. El número de la Factura de venta
  3. El nombre del cliente
  4. La cantidad de productos distintos que le vendimos
  5. El total de la Factura
SELECT
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   L.CLI_NOMBRE,
   D.CANTIDAD,
   D.TOTALVENTA
FROM
   MOVIMCAB C
JOIN
   (SELECT
       MOV_IDECAB,
       COUNT(*) AS CANTIDAD,
       SUM(MOV_CANTID * MOV_PRECIO) AS TOTALVENTA
    FROM
       MOVIMDET
    GROUP BY
       MOV_IDECAB) AS D
   ON C.MVC_IDENTI = D.MOV_IDECAB
JOIN
   CLIENTES L
      ON C.MVC_IDECLI = L.CLI_IDENTI

Como puedes ver, ahora la subconsulta es más compleja pero también mucho más útil: ya nos da la información que estábamos necesitando.

Nuestra tabla MOVIMCAB tiene estas filas:

JOIN1

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

Nuestra tabla MOVIMDET tiene estas filas:

JOIN2

 

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

Nuestra tabla de CLIENTES tiene estas filas:

JOIN3

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

Y el resultado que obtenemos es el siguiente:

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

Entonces aquí tenemos lo que estábamos buscando.

Desde luego que no es la única forma de resolver este problema pero este ejemplo sirve para ilustrar el concepto de que podemos tener subconsultas en un JOIN y que eso puede sernos muy útil en algunas ocasiones. En otras palabras, tenemos una técnica más para utilizar con los SELECTs.

Conclusión:

Podemos tener subconsultas en los JOINs y eso es algo muy bueno porque nos abre un buen abanico de posibilidades. Lo que debemos recordar es que la subconsulta debe estar rodeada por paréntesis y que debemos asignarle un alias. Solamente eso, el resto es igual a los demás JOINs que ya conocemos.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Cuando usar el predicado EXISTS()

1 comentario

Firebird dispone de un predicado llamado EXISTS() el cual nos dice si el resultado de un SELECT tiene al menos una fila.

La forma general de usarlo es la siguiente:

EXISTS (MiConsulta)

Ejemplo 1:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS P
WHERE
   EXISTS(SELECT
             D.MOV_IDEPRD
          FROM
             MOVIMDET D
          WHERE
             D.MOV_IDEPRD = P.PRD_IDENTI AND
             D.MOV_CANTID >= 10)

Esta consulta nos mostrará todos los productos cuya cantidad vendida en una Factura sea mayor o igual que 10.

Ejemplo 2:

BEGIN

   IF (EXISTS(SELECT MiColumna FROM MiTabla WHERE MiCondición)) THEN
      -- Se cumplió la condición
   ELSE
      -- No se cumplió la condición
   END

END

 Esta construcción usaríamos dentro de un stored procedure, un trigger o un EXECUTE BLOCK.

Verificamos si se cumple la condición y luego de acuerdo al resultado realizamos una acción u otra.

Comentarios:

El predicado EXISTS() es mucho más rápido que la función agregada COUNT(*) porque EXISTS() finaliza cuando encuentra una fila que cumple la condición en cambio COUNT(*) cuenta todas las filas.

Así, si por ejemplo una tabla tiene 1.000.000 de filas, y la segunda fila cumple la condición entonces allí mismo ya finaliza EXISTS() y no recorre todas las otras filas porque no es necesario, ya sabe que la condición se cumplió. Pero COUNT(*) debe recorrer a las 1.000.000 de filas para saber cual es esa cantidad.

¿Cuándo es eficiente usar el predicado EXISTS()?

Usar EXISTS() a veces es una buena opción y a veces es una opción pésima ¿por qué? porque EXISTS() va recorriendo secuencialmente todas las filas de la tabla hasta encontrar una que cumpla la condición. Eso implica que si la tabla tiene pocas filas entonces EXISTS() finalizará rápidamente pero si la tabla tiene muchas filas entonces EXISTS() se puede demorar una eternidad si no existe lo buscado o existe pero está cerca del final.

Por lo tanto, si vas a usar el predicado EXISTS() debes preguntarte:

  • ¿Qué tan rápido es el hardware?
  • ¿Cuántas filas tiene la tabla AHORA?
  • ¿Cuántas filas tendrá la tabla dentro de un año, dos años, tres años, …, ocho años?
  • ¿Cuánto tiempo puede demorar la consulta sin que el usuario quiera romper una silla de la bronca?

En general, y al momento de escribir este artículo, si una tabla tiene 100.000 filas o menos entonces se puede usar EXISTS() sin mayor problema. Desde luego que a medida que vaya pasando el tiempo esa cantidad de filas se incrementará porque el hardware será cada vez más y más rápido.

Si sabes que tu tabla tiene o tendrá o puede llegar a tener varios millones de filas, entonces no uses el predicado EXISTS() porque la consulta será extremadamente lenta.

Así que, hoy por hoy, si estás 100% seguro de que la tabla nunca alcanzará a las 100.000 filas, puedes usar EXISTS() sin problema. Pero si la tabla tiene, tendrá, o puede llegar a tener más de 100.000 filas entonces verifica la velocidad con la computadora más lenta que tienes disponible y si obtienes un tiempo de respuesta aceptable.

Artículos relacionados:

Los predicados existenciales

El índice del blog Firebird21

 

Older Entries