Fechas aleatorias

Deja un comentario

A veces cuando estamos realizando pruebas necesitamos datos aleatorios, o sea datos que pueden ser cualesquiera, no podemos anticipar cuales serán.

Supongamos que necesitamos fechas aleatorias. Para ello, como de costumbre usaremos la función RAND(). Esta función nos devolverá un número entre 0 y 0,999999. También usaremos la función FLOOR() que nos devuelve la parte entera de un número. También usaremos la función DATEADD() que le suma un número a una fecha para devolvernos una nueva fecha, la fecha original más la cantidad de días que le hemos sumado.

Veamos algunos ejemplos:

Listado 1. Fechas entre el 1 de enero de 2017 y el 31 de marzo de 2017

SELECT
   DATEADD(FLOOR(90 * RAND()) DAY TO DATE '2017-JAN-01')
FROM
   RDB$DATABASE

Explicación:

La cantidad de días posibles es 90, así que multiplicamos a RAND() por 90. La cantidad de días debe ser un número entero, así que usamos la función FLOOR(), porque la función RAND() nos devuelve un número con decimales. Lo que estamos sumando son días así que usamos DAY, la fecha la expresamos como un string así que debemos convertirla a tipo fecha con DATE.

En síntesis, lo que estamos diciendo es: muéstrame una fecha aleatoria entre el 1 de enero de 2017 y el 31 de marzo de 2017

Listado 2. Fechas entre el 1 de enero de 2017 y el 31 de diciembre de 2017

SELECT
   DATEADD(FLOOR(365 * RAND()) DAY TO DATE '2017-JAN-01')
FROM
   RDB$DATABASE

Explicación:

Como la cantidad de días posibles es 365, entonces multiplicamos a 365 por RAND()

Listado 3. Fechas entre el 1 de abril de 2017 y el 30 de abril de 2017

SELECT
   DATEADD(FLOOR(30 * RAND()) DAY TO DATE '2017-APR-01')
FROM
   RDB$DATABASE

Explicación:

Como la cantidad de días posibles es 30, entonces multiplicamos 30 por RAND(). Como la fecha inicial es el 1 de abril de 2017, entonces escribimos esa fecha después de DATE.

Artículos relacionados:

La función DATEADD()

La función FLOOR()

La función RAND()

El índice del blog Firebird21

El foro del blog Firebird21

Agrupando por una columna … y viendo todas las demás columnas

Deja un comentario

Como seguramente ya sabes, puedes usar la cláusula GROUP BY para agrupar filas de una tabla. Todo bien hasta ahí pero pronto descubrirás que a veces te encuentras con un problema … en tu SELECT solamente puedes ver las columnas que has agrupado.

Ejemplo: Tenemos una tabla llamada VENTASCAB (cabecera de ventas) que tiene los siguientes datos:

AGRUPAR1

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

Queremos ver sus columnas VTC_NRODOC, VTC_FECHAX, VTC_IDECLI y VTC_TOTALX pero solamente cuando la fecha está repetida. En otras palabras, si en una fecha se hicieron dos o más ventas queremos ver esas filas; si se hizo una sola venta, no queremos ver.

Listado 1. (no funcionará)

SELECT
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI,
   VTC_TOTALX
FROM
   VENTASCAB
GROUP BY
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI,
   VTC_TOTALX
HAVING
   COUNT(*) >= 2

Si ejecutamos el Listado 1. no obtendremos ni siquiera una fila. ¿Por qué no? porque estamos agrupando por VTC_NRODOC y el contenido de esa columna nunca se repite.

Listado 2. (no funcionará)

SELECT
   VTC_FECHAX
FROM
   VENTASCAB
GROUP BY
   VTC_FECHAX
HAVING
   COUNT(*) >= 2

Si ejecutamos el Listado 2. obtendremos la Captura 2. pero … no es lo que estamos necesitando.

AGRUPAR2

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

En la Captura 2. hemos agrupado por fechas que se repiten 2 ó más veces pero no podemos ver las columnas VTC_NRODOC, VTC_IDECLI, y VTC_TOTALX, y queremos verlas, entonces ¿cómo lo conseguimos?

Usando una gran construcción del Firebird que es la subconsulta filtrada. O sea, una subconsulta que se utiliza en la cláusula WHERE.

Listado 3. (Sí funcionará)

SELECT
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI,
   VTC_TOTALX
FROM
   VENTASCAB
WHERE
   VTC_FECHAX IN (SELECT
                     VTC_FECHAX
                  FROM
                     VENTASCAB
                  GROUP BY
                     VTC_FECHAX
                  HAVING
                     COUNT(*) >= 2)

Si ejecutamos el Listado 3. obtendremos la Captura 3. que nos mostrará exactamente lo que estábamos necesitando.

AGRUPAR3

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

O sea que si la fecha está repetida, queremos ver los datos de cada una de las ventas realizadas en esa fecha.

Si te fijas, notarás que la subconsulta del Listado 3. es igual a la consulta del Listado 2. ¿Qué estamos pidiendo en el Listado 3.? Que nos muestre las filas de la tabla VENTASCAB cuyas fechas se obtengan de la subconsulta. O sea, las fechas mostradas en la Captura 2. Por lo tanto, solamente si una fecha se encuentra en la Captura 2. esa fila será mostrada.

Conclusión:

Una subconsulta filtrada puede ser extremadamente útil en muchas circunstancias y debemos aprender a utilizarla. Lo que hacemos con ella es filtrar a una tabla o una vista para que solamente se utilicen las filas que cumplen con la condición que le imponemos.

Como en nuestro ejemplo la fecha debe estar repetida entonces debemos filtrar usando la columna de fecha (VTC_FECHAX se llama en esta tabla). Desde luego que podemos filtrar por cualquier otra columna, siempre y cuando tenga valores repetidos esta técnica funcionará.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Velocidad de acceso a constantes, variables y columnas

Deja un comentario

A veces tenemos la alternativa de usar en un SELECT una constante, una variable, o el valor de una columna. Desde el punto de vista de la velocidad ¿hay diferencias entre cuál de ellas usar?

Alternativa 1. Usando una constante.

SELECT
   MiColumna
FROM
   MiTabla
WHERE
   MiColumna = 4567

Alternativa 2. Usando una variable.

SELECT
   MiColumna
FROM
   MiTabla
WHERE
   MiColumna = RDB$GET_CONTEXT('USER_SESSION', 'MIVALOR')

Alternativa 3. Usando el valor de una columna.

SELECT
   MiColumna
FROM
   MiTabla
WHERE
   MiColumna = (SELECT MiColumnaInteresante FROM MiOtraTabla)

Y en este caso MiOtraTabla tiene una sola fila.

¿Cuál de estas tres alternativas es la más rápida?

La más rápida es la Alternativa 1., luego le sigue la Alternativa 2. y finalmente la Alternativa 3, la más lenta de todas.

Claro que las diferencias de velocidad se miden en milisegundos así que en la práctica muchas veces será lo mismo si utilizas cualquiera de esas alternativas.

Artículos relacionados:

Usando variables en Firebird

El índice del blog Firebird21

 

Usando UNION para reemplazar condiciones complejas

Deja un comentario

En ocasiones tenemos que escribir un SELECT que tiene una cláusula WHERE muy compleja, está llena de condiciones, de AND, de OR, y entender lo que hace nos resulta bastante complicado. Una forma de facilitar la lectura es usando la cláusula UNION.

Usando UNION es similar a usar OR pero:

  1. Se escribe más al usar UNION que al usar OR
  2. Se entiende mejor al usar UNION que al usar OR cuando las condiciones son complejas

Ejemplo:

Tenemos una tabla llamada CLIENTES que tiene estas filas:

UNION1

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

Y queremos una consulta que nos muestre los clientes cuyos nombres empiecen con la letra “M” o con la letra “S”. Podríamos escribir una consulta como esta:

SELECT
   CLI_NOMBRE
FROM
   CLIENTES
WHERE
   CLI_NOMBRE STARTING WITH 'M' OR
   CLI_NOMBRE STARTING WITH 'S'

Y está muy bien, como la consulta es simple esa es la solución adecuada. Pero si queremos podríamos obtener el mismo resultado usando la cláusula UNION.

SELECT
   CLI_NOMBRE
FROM
   CLIENTES
WHERE
   CLI_NOMBRE STARTING WITH 'M'

UNION

SELECT
   CLI_NOMBRE
FROM
   CLIENTES
WHERE
   CLI_NOMBRE STARTING WITH 'S'

Entonces, la primera forma es la adecuada cuando las condiciones son simples. Usamos OR y asunto solucionado. Pero cuando las condiciones son complicadas es preferible usar UNION porque nos facilitará la lectura y nos resultará más fácil comprender que hace nuestro SELECT.

Conclusión:

Se puede usar la cláusula UNION para reemplazar al operador condicional OR pero solamente se justifica usar UNION cuando las condiciones son complicadas de entender, ya que al usar UNION es fácil entender lo que está ocurriendo.

Podemos pensar en UNION como en una suma. Nos devuelve el resultado de la primera consulta MÁS el resultado de la segunda consulta.

En nuestro ejemplo, los nombres de los clientes que empiezan con “M” más los nombres de los clientes que empiezan con “S”.

Artículos relacionados:

Entendiendo a las UNION

El índice del blog Firebird21

Eligiendo entre LIKE y CONTAINING en los SELECT

5 comentarios

Cuando en una consulta la condición de búsqueda es alfanumérica podemos usar (entre otras posibilidades)  a LIKE y a CONTAINING.

¿Cuál es preferible?

LIKE distingue entre mayúsculas y minúsculas, en cambio CONTAINING no las distingue.

Ejemplo:

Tenemos una tabla llamada BANCOS con estas filas:

LIKE1

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

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_CODSUC >= 0 AND
   BAN_NOMBRE LIKE '%BAN%'

LIKE2

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

En la Captura 2 podemos ver cuales son las filas que obtuvimos al usar LIKE.

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_CODSUC >= 0 AND
   BAN_NOMBRE CONTAINING 'BAN'

LIKE3

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

Y en la Captura 3 las filas que obtenemos al usar CONTAINING.

Como puedes ver, con CONTAINING obtuvimos más filas porque nos muestra las que están en mayúsculas y también las que están en minúsculas. Por eso en general, cuando los nombres no están estandarizados es preferible usar CONTAINING.

Esto sucede a menudo cuando son varias las personas encargadas de registrar los datos en una tabla. Algunos escriben todo en mayúsculas y algunos mezclan mayúsculas con minúsculas. Entonces, para asegurarnos que encontremos todos los datos buscados, sea que los hayan escrito en mayúsculas o en minúsculas debemos usar CONTAINING.

Artículo relacionado:

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

 

Consultas con columnas condicionales

2 comentarios

A veces en nuestras consultas necesitamos mostrar columnas cuyos contenidos provienen de otras tablas y además lo hacen en forma condicional.

Ejemplo:

En una tabla MOVIMCAB tenemos una columna llamada MVC_TIPMOV la cual nos indica el tipo de movimiento (‘ECM’ es una entrada por compras, ‘SVT’ es una salida por ventas, etc.) y donde también guardamos el Identificador del Proveedor (cuando se trata de una compra) o el Identificador del Cliente (cuando se trata de una venta).

Ahora, necesitamos una consulta que nos muestre el nombre del Proveedor o el nombre del Cliente, según corresponda. Para ello haremos uso de la construcción CASE … WHEN … END

SELECT
   MVC_FECHAX,
   MVC_NRODOC,
   MVC_TIPMOV,
   CASE
      WHEN MVC_TIPMOV = 'ECM' THEN (SELECT PRO_NOMBRE FROM PROVEEDORES WHERE PRO_IDENTI = MVC_IDEPRO)
      WHEN MVC_TIPMOV = 'SVT' THEN (SELECT CLI_NOMBRE FROM CLIENTES    WHERE CLI_IDENTI = MVC_IDECLI)
   END AS NOMBRE
FROM
   MOVIMCAB
WHERE
   MVC_TIPMOV IN ('ECM', 'SVT')

Y este es el resultado (parcial, por supuesto) que obtuvimos:

CONDICIONAL1

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

Como puedes ver, en la columna NOMBRE tenemos el nombre de un Proveedor (cuando se trata de una compra) o el nombre de un Cliente (cuando se trata de una venta). Y para saber de cual de ellos se trata podemos mirar el contenido de la columna MVC_TIPMOV.

ERROR: Multiple rows in singleton select

Si ves ese mensaje de error significa que el SELECT interno devolvió más de una fila pero debería haber devuelto solamente una fila (o ninguna, pero jamás más de una).

¿Cuál es el problema?

Que si el SELECT devuelve varias filas el Firebird no puede saber cual de esas filas es la correcta. Por ejemplo, si el SELECT devuelve el nombre de 500 clientes ¿cuál de esos 500 nombres es el que debe mostrar? imposible que el Firebird pueda saberlo y por lo tanto te muestra un mensaje de error.

Para evitar el error siempre tienes que asegurarte que la condición que pusiste en la cláusula WHERE limite el resultado a una sola fila.

En el ejemplo de arriba se usó el Identificador del Proveedor o del Cliente. Como esos identificadores son únicos y jamás pueden repetirse entonces estamos seguros de que jamás obtendremos el error “multiple rows in singleton select”. Sin embargo cuando no usas identificadores ni claves primarias ni claves únicas la posibilidad de encontrar ese error siempre existirá.

Artículo relacionado:

El índice del blog Firebird21

Consultando datos que NO EXISTEN en una tabla

1 comentario

En las consultas lo normal es responder preguntas positivas, por ejemplo: ¿qué productos se vendieron hoy? ¿a cuáles clientes se les cobró hoy? ¿cuáles vendedores hicieron ventas hoy?

Todas esas preguntas son positivas porque los datos están guardados en las tablas y para responderlas lo que hacemos es consultar datos que se guardaron en las tablas.

Pero a veces necesitamos responder preguntas negativas, por ejemplo: ¿cuáles productos NO se vendieron hoy? ¿a cuáles clientes NO se les cobró hoy? ¿cuáles vendedores NO hicieron ventas hoy?

En Matemática, en la teoría de conjuntos, a esto se le llama “diferencia de conjuntos” y en Firebird tenemos dos formas de conseguirlo:

  1. Negamos la función EXISTS() para hallar la diferencia entre dos conjuntos
  2. Escribimos un LEFT JOIN y un NULL en la cláusula WHERE

Ejemplo:

Tenemos una tabla llamada PRODUCTOS que tiene estas filas:

DIFERENCIA1

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

y una tabla llamada MOVIMDET (detalles de los movimientos) que tiene estas filas:

DIFERENCIA2

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

donde en la columna MOV_IDEPRD se guarda el identificador del producto. Usamos esa columna para relacionar ambas tablas.

Método 1

Para saber cuales fueron los productos que NO SE VENDIERON escribimos esta consulta:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE
FROM
   PRODUCTOS P
WHERE
   NOT EXISTS(SELECT
                 M.MOV_IDEPRD
              FROM
                 MOVIMDET M
              WHERE
                 M.MOV_IDEPRD = P.PRD_IDENTI)

y obtenemos este resultado:

DIFERENCIA3

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

donde como puedes ver se encuentran los productos que NO ESTÁN en la tabla MOVIMDET. En la tabla MOVIMDET los productos tienen los identificadores: 13, 14, 15, 16 y ningún producto con alguno de esos identificadores es mostrado en el resultado que obtuvimos.

Método 2

Podríamos obtener exactamente el mismo resultado escribiendo:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE
FROM
   PRODUCTOS P
LEFT JOIN
   MOVIMDET M
      ON P.PRD_IDENTI = M.MOV_IDEPRD
WHERE
   M.MOV_IDEPRD IS NULL

Conclusión:

Con Firebird podemos responder preguntas positivas y preguntas negativas, hay que conocer las técnicas para responder las preguntas negativas porque pueden sernos útiles muchas veces.

Artículos relacionados:

Teoría de Conjuntos: Unión, Intersección, Diferencia

El índice del blog Firebird21

Facturas que vencen hoy, en 7 días, en 15 días y en 30 días

Deja un comentario

Si necesitamos consultar cuales son las Facturas que debemos cobrar hoy, que debemos cobrar en 7 días, en 15 días, en 30 días (y por supuesto, en cualquier otra fecha) podemos hacerlo de varias maneras, aquí hay cuatro de ellas:

Ejemplo:

Tenemos una tabla llamada FACTURAS donde se encuentran las fechas de vencimiento de las Facturas de venta, la tabla tiene esta estructura:

FACTURAS1

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

y contiene estas filas:

FACTURAS2

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

Método 1. Usando UNION

Podemos usar el comando UNION para unir el resultado de una consulta con el resultado de otra consulta. En este caso, para diferenciar los resultados y saber a cual consulta pertenecen los resultados debemos agregarle una columna a cada consulta. En este ejemplo esa columna agregada se llama FECHA.

CREATE VIEW V_VENCIMIENTOS(
   FECHA,
   FAC_FECVEN,
   FAC_NUMERO,
   FAC_MONTOX)
AS

   SELECT
      'HOY' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN = CURRENT_DATE

   UNION

   SELECT
      'SEMANA' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
     FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6

   UNION

   SELECT
      'QUINCENA' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14

   UNION

   SELECT
      'MES' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30;

Aquí, a las Facturas que vencen hoy les unimos las que vencen en la semana, les unimos las que vencen en la quincena y les unimos las que vencen en el mes. Se usa la cláusula BETWEEN para delimitar las fechas de las Facturas que serán mostradas y evitar así que una Factura se muestre más de una vez.

Escribimos esta consulta:

SELECT * FROM V_VENCIMIENTOS ORDER BY FAC_FECVEN

Y este es el resultado que obtenemos:

FACTURAS3

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

Donde cada Factura aparece en una fila y en la primera columna podemos ver si su vencimiento corresponde al día de hoy, a la semana, a la quincena o al mes.

Método 2. Usando un stored procedure

Aquí, en lugar de mostrar los vencimientos en filas los mostramos en columnas porque para algunas personas puede ser más descriptivo de esta manera:

CREATE PROCEDURE VENCIMIENTOS
   RETURNS(
      ftcNumero1 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto1  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero2 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto2  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero3 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto3  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero4 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto4  TYPE OF COLUMN FACTURAS.FAC_MONTOX)
AS
   DECLARE VARIABLE lcNumero TYPE OF COLUMN FACTURAS.FAC_NUMERO;
   DECLARE VARIABLE lnMontox TYPE OF COLUMN FACTURAS.FAC_MONTOX;
   DECLARE VARIABLE ldFecVen TYPE OF COLUMN FACTURAS.FAC_FECVEN;
BEGIN

   FOR SELECT
      FAC_NUMERO,
      FAC_MONTOX,
      FAC_FECVEN
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
   ORDER BY
      FAC_FECVEN
   INTO
      :lcNumero,
      :lnMontox,
      :ldFecVen
   DO BEGIN
      ftcNumero1 = NULL;
      ftnMonto1  = NULL;
      ftcNumero2 = NULL;
      ftnMonto2  = NULL;
      ftcNumero3 = NULL;
      ftnMonto3  = NULL;
      ftcNumero4 = NULL;
      ftnMonto4  = NULL;
      IF (ldFecVen = CURRENT_DATE) THEN BEGIN
         ftcNumero1 = lcNumero;
         ftnMonto1  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6) THEN BEGIN
         ftcNumero2 = lcNumero;
         ftnMonto2  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14) THEN BEGIN
         ftcNumero3 = lcNumero;
         ftnMonto3  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30) THEN BEGIN
         ftcNumero4 = lcNumero;
         ftnMonto4  = lnMontox;
      END
      SUSPEND;
   END

END;

Como se trata de un stored procedure seleccionable lo ejecutamos así:

SELECT * FROM VENCIMIENTOS

Y este será el resultado que obtendremos:

FACTURAS3

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

Si no quieres ver los Null puedes usar la función COALESCE() para que desaparezcan.

Método 3. Usando una vista con una tabla CTE

Ya hemos visto en artículos anteriores lo útiles que pueden ser las tablas CTE, aquí también pueden ser usadas:

CREATE VIEW V_VENCIMIENTOS(
   FAC_FECVEN,
   HOY,
   FAC_MONTOX,
   SEMANA,
   FAC_MONTOX1,
   QUINCENA,
   FAC_MONTOX2,
   MES,
   FAC_MONTOX3)
AS

   WITH FacturasVencen AS (
      SELECT
         FAC_NUMERO,
         FAC_MONTOX,
         FAC_FECVEN
      FROM
         FACTURAS
   )

   SELECT
      F.FAC_FECVEN,
      F1.FAC_NUMERO AS "HOY",
      F1.FAC_MONTOX,
      F2.FAC_NUMERO AS "SEMANA",
      F2.FAC_MONTOX,
      F3.FAC_NUMERO AS "QUINCENA",
      F3.FAC_MONTOX,
      F4.FAC_NUMERO AS "MES",
      F4.FAC_MONTOX
   FROM
      FACTURAS F
   LEFT JOIN
      FacturasVencen F1
         ON F.FAC_NUMERO = F1.FAC_NUMERO AND
            F1.FAC_FECVEN = CURRENT_DATE
   LEFT JOIN
      FacturasVencen F2
         ON F.FAC_NUMERO = F2.FAC_NUMERO AND
            F2.FAC_FECVEN BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6
   LEFT JOIN
      FacturasVencen F3
         ON F.FAC_NUMERO = F3.FAC_NUMERO AND
            F3.FAC_FECVEN BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14
   LEFT JOIN
      FacturasVencen F4
         ON F.FAC_NUMERO = F4.FAC_NUMERO AND
            F4.FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30
   WHERE
      F1.FAC_FECVEN IS NOT NULL OR
      F2.FAC_FECVEN IS NOT NULL OR
      F3.FAC_FECVEN IS NOT NULL OR
      F4.FAC_FECVEN IS NOT NULL;

Y al ejecutar a esa vista con este comando:

SELECT * FROM V_VENCIMIENTOS ORDER BY FAC_FECVEN

Este será el resultado que obtendremos:

FACTURAS4

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

Si no quieres ver los Null puedes usar la función COALESCE() para que desaparezcan.

Método 4. Usando una tabla en el FROM

También puedes ver las Facturas en filas con esta consulta:

SELECT
   *
FROM (
   SELECT
      CASE WHEN FAC_FECVEN = CURRENT_DATE THEN 'HOY'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE +  1 AND CURRENT_DATE +  6 THEN 'SEMANA'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE +  7 AND CURRENT_DATE + 14 THEN 'QUINCENA'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30 THEN 'MES'
      END AS FECHA_VENCIMIENTO,
      FAC_NUMERO,
      FAC_FECVEN,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
 ) AS MiTabla
ORDER BY
   FAC_FECVEN

Y este será el resultado obtenido:

FACTURAS5

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

Conclusión:

Con todos los métodos obtenemos las Facturas que debemos cobrar pero la forma de salida de los resultados es diferente, a veces una de las salidas es la preferible. También en algunos métodos se escribe mucho más que en otros.

Artículo relacionado:

El índice del blog Firebird21

Convirtiendo filas en columnas

Deja un comentario

Normalmente cuando escribimos un SELECT los resultados se muestran en una fila tras otra pero a veces podría interesarnos ver los resultados en una columna tras otra, o sea: convertir las filas en columnas.

Una forma de conseguirlo es con las tablas CTE (Common Table Expression) que como ya hemos visto en artículos anteriores son tablas virtuales.

Desde luego que para que esto sea manejable la cantidad de filas que convertiremos en columnas debe ser pequeña. Si nuestro SELECT nos devuelve miles o millones de filas no vamos a convertirlas a todas ellas en columnas, nadie siquiera miraría semejante monstruosidad y habríamos desperdiciado nuestro tiempo.

Ejemplo:

Tenemos una tabla de BANCOS que tiene 16 filas y queremos ver las primeras 4 filas como columnas.

COLUMNAS1

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

Para ello escribimos la siguiente consulta:

WITH MisBancos AS (
   SELECT
      BAN_CODSUC,
      BAN_IDENTI,
      BAN_NOMBRE
   FROM
      BANCOS
)

SELECT
   M.BAN_NOMBRE,
   N.BAN_NOMBRE,
   O.BAN_NOMBRE,
   P.BAN_NOMBRE
FROM
   BANCOS B
JOIN
   MISBANCOS M
      ON B.BAN_CODSUC = M.BAN_CODSUC AND
         B.BAN_IDENTI + 0 = M.BAN_IDENTI AND
         M.BAN_IDENTI = 1
JOIN
   MISBANCOS N
      ON B.BAN_CODSUC = N.BAN_CODSUC AND
         B.BAN_IDENTI + 1 = N.BAN_IDENTI AND
         N.BAN_IDENTI = 2
JOIN
   MISBANCOS O
      ON B.BAN_CODSUC = O.BAN_CODSUC AND
         B.BAN_IDENTI + 2 = O.BAN_IDENTI AND
         O.BAN_IDENTI = 3
JOIN
   MISBANCOS P
      ON B.BAN_CODSUC = P.BAN_CODSUC AND
         B.BAN_IDENTI + 3 = P.BAN_IDENTI AND
         P.BAN_IDENTI = 4
WHERE
   B.BAN_CODSUC = 0 AND
   B.BAN_IDENTI > 0
ROWS
 1

Y este es el resultado que obtenemos:

COLUMNAS2

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

Donde como puedes ver los nombres de los Bancos se muestran en columnas diferentes.

Otro método:

Podemos obtener el mismo resultado anterior usando una tabla que siempre tenga una fila y nunca más ni menos de una fila. Aquí usamos la tabla interna RDB$DATABASE porque está garantizada que siempre tendrá exactamente una fila pero tú puedes usar cualquier otra tabla si quieres.

WITH MisBancos AS (
   SELECT
      BAN_CODSUC,
      BAN_IDENTI,
      BAN_NOMBRE
   FROM
      BANCOS
)

SELECT
   M.BAN_NOMBRE,
   N.BAN_NOMBRE,
   O.BAN_NOMBRE,
   P.BAN_NOMBRE
FROM
   RDB$DATABASE
JOIN
   MISBANCOS M
      ON M.BAN_IDENTI = 1
JOIN
   MISBANCOS N
      ON N.BAN_IDENTI = 2
JOIN
   MISBANCOS O
      ON O.BAN_IDENTI = 3
JOIN
   MISBANCOS P
      ON P.BAN_IDENTI = 4

¿Cuál es el truco?

  1. Crear una tabla CTE
  2. Para cada columna que deseamos mostrar hacerle un JOIN a nuestra tabla principal con la tabla CTE. En ese JOIN establecer una relación entre ellas (en este ejemplo esa relación estaba dada por los valores de la columna BAN_IDENTI).

Artículos relacionados:

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando una cross-table

¿Por qué usar una cross-table?

Usando varias CTE en una vista o en un stored procedure

El índice del blog Firebird21

Older Entries