Ejemplo de recursión (3). Fechas consecutivas

Ya hemos visto varios ejemplos de tablas virtuales recursivas, pero como es un tema que le interesa a mucha gente entonces ahora veremos otro ejemplo de lo que podemos hacer.

Problema:

Queremos ver todas las fechas desde hoy hasta los siguientes 10 días.

Solución:

Listado 1.

WITH RECURSIVE FECHAS_SIGUIENTES AS (
   
   SELECT
      CURRENT_DATE AS FECHA
   FROM
      RDB$DATABASE

   UNION ALL
   
   SELECT
      DATEADD(DAY, 1, FECHA)
   FROM
      FECHAS_SIGUIENTES
   WHERE
      FECHA < DATEADD(DAY, 10, CURRENT_DATE)

)

SELECT
   *
FROM
   FECHAS_SIGUIENTES

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

Como siempre, nuestra tabla virtual recursiva empieza con una fila no recursiva y luego se le agregan las filas recursivas. La filas recursivas siempre deben colocarse después del UNION ALL.

El algoritmo es muy sencillo. Primero, insertamos a nuestra tabla virtual la primera fila que nos interesa, luego le insertamos otra fila conteniendo la fecha del día siguiente y continuamos insertando filas mientras la fecha obtenida sea menor que la fecha actual + 10.

Desde luego que podemos usar otro número, 10 es solamente un ejemplo. Podríamos obtener 30 fechas, 60 fechas, 365 fechas, o las que necesitemos, siempre y cuando su cantidad no sea mayor a 1024 porque ese es el límite de llamadas recursivas que permite el Firebird.

¿Y para qué nos podría servir tener una tabla virtual de fechas?

Por ejemplo, para listar todas las ventas entre dos fechas, y si en una fecha no se ha realizado ventas que muestre cero. Así nos aseguraríamos que estén todas las fechas, sin que existan fechas faltantes.

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

Como puedes ver en la Captura 1. no hubo ventas todos los días, por ejemplo no hay ventas el 1 de agosto ni el 3 de agosto. En nuestra consulta queremos que esas dos fechas también aparezcan, aunque no hayamos vendido.

Listado 2.

WITH RECURSIVE RANGO_FECHAS AS (

   SELECT
      CAST('2015/08/01' AS DATE) AS FECHA
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      DATEADD(DAY, 1, FECHA)
   FROM
      RANGO_FECHAS
   WHERE
      FECHA < CAST('2015/08/20' AS DATE)

)

SELECT
   FECHA,
   COALESCE(SUM(FAC_MONTOX), 0) AS TOTAL_VENTA
FROM
   RANGO_FECHAS
LEFT JOIN
   FACTURAS
      ON FECHA = FAC_FECVEN
GROUP BY
   FECHA

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

Y listo. ¡¡¡Solucionado!!!

Como puedes ver en la Captura 2. se muestran todas las fechas entre el 1 de agosto y el 20 de agosto (porque esas fueron las que elegimos, podríamos haber elegido cualquier otro rango de fechas) con el total vendido cada uno de esos días. Si en una fecha no hubo ventas, entonces se muestra cero.

Conclusión:

Poder listar todo un rango de fechas es muy útil cuando queremos ver a todas esas fechas, sin importar que en la tabla relacionada haya o no haya fechas que se puedan emparejar. Tal como nos muestra la Captura 2. si en una fecha no hubo ventas, igualmente esa fecha es mostrada.

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

El índice del blog Firebird21

El foro del blog Firebird21

Creando una subconsulta que devuelva muchas columnas

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

Usando recursividad con CTE

Una técnica muy poderosa aunque muy poco usada es la de la recursividad. ¿Qué significa recursividad? Que un código se ejecute y al finalizar se llame a sí mismo para volver a ejecutarse. Sin embargo, al volver a ejecutarse lo hace con algún parámetro cambiado y continúa llamándose a sí mismo hasta que se cumpla una condición de fin. Sin esa condición de fin se ejecutaría infinitas veces (o más propiamente, hasta que usara toda la memoria disponible de la computadora).

En Firebird podemos tener stored procedures recursivos y también SELECTs recursivos.

Los stored procedures recursivos ya los vimos en el artículo:

Stored procedures recursivos

Así que ahora veremos algo también muy interesante, los SELECTs recursivos.

Para crear un SELECT recursivo debemos usar CTE (Common Table Expression) porque esa construcción permite recursividad.

Ejemplo:

Tenemos una tabla llamada GENEALOGIA donde guardamos los nombres de las personas y nos gustaría conocer quienes son los ascendientes de cada persona (o sea, sus padres, abuelos, bisabuelos, tatarabuelos, etc.)

Algo así podríamos conseguir con tablas autoreferenciadas, como vimos en el artículo:

Entendiendo las tablas autoreferenciadas

y también con recursividad, que es el tema que ahora nos ocupa.

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

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

En la columna GEN_IDEPAD se guarda el Identificador de la fila padre. Una fila puede no tener fila padre (como es el caso de «ALICIA» o puede tener una sola fila padre (los demás casos).

Gráficamente, lo que tenemos es esto:

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

Aquí podemos ver que BRIGIDA, BEATRIZ y BENITA son hijas de ALICIA; y que CORINA, CAROL, CARMEN y CLAUDIA son hijas de BRIGIDA y que DALILA y DIANA son hijas de CAROL.

Entonces, para ver el nombre de una persona y de todos sus ascendientes podríamos escribir algo como:

Listado 1.

WITH RECURSIVE PARIENTES AS (
   
   SELECT 
      * 
   FROM 
      GENEALOGIA 
   WHERE 
      GEN_IDENTI = 10
   
   UNION ALL
   
   SELECT 
      GENEALOGIA.* 
   FROM 
      GENEALOGIA 
   JOIN 
      PARIENTES 
         ON GENEALOGIA.GEN_IDENTI = PARIENTES.GEN_IDEPAD
   
)

SELECT
   * 
FROM 
   PARIENTES
ORDER BY
   GEN_IDENTI

Aquí hemos pedido la genealogía de la persona con Identificador igual a 10, entonces obtendremos lo siguiente:

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

Explicación:

Con la instrucción WITH RECURSIVE PARIENTES AS ( … ) hemos creado una tabla virtual, o sea una tabla que solamente existe en la memoria de la computadora, cuyo nombre es PARIENTES y que es recursiva. ¿Qué significa que sea recursiva? que mientras pueda irá ejecutando los comandos que hayamos colocado dentro suyo.

¿Y cuál es la condición de fin?

Siempre que usamos recursividad debemos colocar una condición de fin, o de lo contrario se ejecutará infinitas veces (o mejor dicho, mientras tenga memoria disponible y luego se «colgará»). En este caso nuestra condición de fin lo da el JOIN, mientras una fila tenga algún valor en la columna GEN_IDEPAD continuará la ejecución, cuando ya no tenga valor (o sea, cuando GEN_IDEPAD sea NULL) finalizará porque ya no se cumplicará la condición que habíamos establecido de que GENEALOGIA.GEN_IDENTI = PARIENTES.GEN_IDEPAD.

¿Y por qué se usó UNION ALL?

Porque el primer SELECT es el que nos dice desde donde debemos empezar y en una CTE recursiva siempre hay que usar UNION ALL para agregar las demás filas que necesitamos. El primer SELECT se ejecuta una sola vez, el segundo SELECT se ejecuta muchas veces. Y si te fijas, en el segundo SELECT se usó el nombre de la tabla CTE que estamos creando, que en este caso es PARIENTES. O sea que dentro de la tabla virtual PARIENTES se usó a la tabla virtual PARIENTES y a eso se le llama … RECURSIVIDAD.

En síntesis:

Para crear una CTE recursiva, debemos:

1. Escribir la palabra RECURSIVE a continuación de la palabra WITH
2. Escribir un SELECT que determine cual será la primera fila, o sea donde empezará la recursividad
3. Escribir UNION ALL
4. Escribir un SELECT que tenga una condición de fin. Esa condición de fin puede colocarse en la cláusula WHERE o en la cláusula JOIN

Una vez que hemos creado a nuestra tabla virtual (o sea, a nuestra tabla CTE) ya la podemos usar como a cualquier otra tabla. Podemos mostrar su contenido (como en el ejemplo de este artículo) o usarla en una subconsulta, en un JOIN, etc.

Conclusión:

La recursividad es una técnica muy poderosa aunque muy poco usada. Debemos pensar en usar recursividad cuando hay una relación directa entre dos filas, lo cual generalmente se da en las tablas que pueden ser autoreferenciadas.

Cuidado especial debemos tomar en asegurarnos de que siempre exista una condición de fin y que a esa condición de fin se llegue alguna vez, de lo contrario a nuestra tabla virtual se le irán agregando filas y más filas, hasta que la computadora se quede sin memoria disponible y en ese momento se «colgará» por falta de memoria.

Hay personas que tienen facilidad para pensar de forma recursiva (como el autor de este blog) y personas a quienes les cuesta mucho pensar así. El no pensar de forma recursiva no es un defecto, todos los algoritmos recursivos también pueden expresarse de otra manera, sin usar recursividad. La ventaja de la recursividad es que en algunos algoritmos (no en todos, en algunos) es muy sencilla de implementar, con pocas líneas se llega al resultado buscado.

Por ejemplo, si quieres obtener los mismos resultados que te muestra el Listado 1. sin usar recursividad desde luego que podrás hacerlo, pero sin dudas que escribirás mucho más. Se notará muchísimo la diferencia cuando los niveles no sean 4 como en nuestro ejemplo sino que sean 12, 20, ó más.

Si aprendemos a usar recursividad entonces tendremos una herramienta más para poder realizar nuestras tareas.

Artículos relacionados:

Stored procedures recursivos

Entendiendo 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

El índice del blog Firebird21

El foro del blog Firebird21

FOR SELECT y tablas CTE

Si en un stored procedure o en un trigger escribimos algo como:

WITH CONSULTAS AS (
   SELECT
      CLI_IDENTI,
      CLI_NOMBRE
   FROM
      CLIENTES
 )

 FOR SELECT

cuando compilamos ese código veremos un mensaje de error: «token unknown FOR»

¿Qué significa ese mensaje?

Que quisimos usar como palabra clave una palabra que para el Firebird no puede ser usada como tal en ese lugar.

¿Y por qué eso?

Porque la tabla CTE es parte del FOR SELECT y por lo tanto debe encontrarse adentro, no afuera. La sintaxis correcta es:

FOR WITH CONSULTAS AS (

o sea que en este caso no se usa un FOR SELECT sino un FOR WITH

Artículo relacionado:

El índice del blog Firebird21

Convirtiendo filas en columnas

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.

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:

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

Usando varias CTE en una vista o en un stored procedure

Si queremos o necesitamos, podemos tener varias tablas virtuales (CTE) en una sola vista o en un solo stored procedure. Para ello debemos declararlas de esta forma:

WITH MiCTE1 AS (
   SELECT ...
),

MiCTE2 AS (
   SELECT ...
)

SELECT
   Columnas
FROM
   MiTablaPrincipal
LEFT JOIN
   MiCTE1
      ON MiCondición1
LEFT JOIN
   MiCTE2
      ON MiCondición2

Como habrás notado, la palabra clave WITH se escribe solamente una vez (antes de la primera CTE) y el carácter de separación entre una CTE y la siguiente es la coma.

Puedes así tener todas las tablas virtuales que quieras, los únicos requisitos son que debes hacer un JOIN a cada una de ellas en tu SELECT principal y que no puedes tener una CTE dentro de otra CTE.

Artículos relacionados:

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando una cross-table

¿Por qué usar una cross-table?

El índice del blog Firebird21

Otro ejemplo de CTE: ventas semanales

En este ejemplo veremos como obtener el total vendido en cada día de una semana usando la construcción CTE. O sea que podremos responder a la pregunta, ¿cuánto se vendió el domingo, cuánto se vendió el lunes, cuánto se vendió el martes…., etc.?

CREATE PROCEDURE VENTAS_SEMANALES(
   tdFechax DATE)
RETURNS(
   ftnVentasDom NUMERIC(10, 2),
   ftnVentasLun NUMERIC(10, 2),
   ftnVentasMar NUMERIC(10, 2),
   ftnVentasMie NUMERIC(10, 2),
   ftnVentasJue NUMERIC(10, 2),
   ftnVentasVie NUMERIC(10, 2),
   ftnVentasSab NUMERIC(10, 2)
AS
   DECLARE VARIABLE ldFecIni DATE;
BEGIN

   ldFecIni = (SELECT :tdFechax - EXTRACT(WEEKDAY FROM :tdFechax) FROM RDB$DATABASE);

   WITH VentasDia AS (
      SELECT
         M.MVC_FECHAX,
         SUM(M.MVC_TOTALX) AS VENTAS
      FROM
         MOVIMCAB M
      WHERE
         MVC_TIPMOV = 'SVT'
      GROUP BY
         1
   )

   SELECT
      COALESCE(D0.VENTAS, 0),
      COALESCE(D1.VENTAS, 0),
      COALESCE(D2.VENTAS, 0),
      COALESCE(D3.VENTAS, 0),
      COALESCE(D4.VENTAS, 0),
      COALESCE(D5.VENTAS, 0),
      COALESCE(D6.VENTAS, 0)
   FROM
      RDB$DATABASE
   LEFT JOIN
      VENTASDIA D0
         ON D0.MVC_FECHAX = :ldFecIni + 0
   LEFT JOIN
      VENTASDIA D1
         ON D1.MVC_FECHAX = :ldFecIni + 1
   LEFT JOIN
      VENTASDIA D2
         ON D2.MVC_FECHAX = :ldFecIni + 2
   LEFT JOIN
      VENTASDIA D3
         ON D3.MVC_FECHAX = :ldFecIni + 3
   LEFT JOIN
      VENTASDIA D4
         ON D4.MVC_FECHAX = :ldFecIni + 4
   LEFT JOIN
      VENTASDIA D5
         ON D5.MVC_FECHAX = :ldFecIni + 5
   LEFT JOIN
      VENTASDIA D6
         ON D6.MVC_FECHAX = :ldFecIni + 6
   INTO
      :ftnVentasDom,
      :ftnVentasLun,
      :ftnVentasMar,
      :ftnVentasMie,
      :ftnVentasJue,
      :ftnVentasVie,
      :ftnVentasSab;

END;

Como ya sabes, una tabla CTE es una tabla virtual o sea que no existe en la realidad sino que es creada antes de usarla y es eliminada después de ser usada. Es muy útil cuando debes hacer más de un JOIN con una tabla que tiene columnas agrupadas o funciones agregadas.

¿Qué hace este stored procedure?

Primero, halla el domingo que corresponde a la semana de la fecha que recibió como parámetro de entrada. Por ejemplo, el día 18 de noviembre de 2013 es lunes, eso significa que el domingo de esa semana es el 17 de noviembre de 2013. Ese día domingo se guarda en la variable ldFecIni. ¿Por qué se hace eso? porque los días de la semana tienen número en Firebird. Domingo = 0, Lunes = 1, Martes, = 2, etc.

A continuación se crea la tabla virtual, la tabla CTE, la cual será usada más adelante en un SELECT. A esa tabla virtual (o tabla CTE) se la llamó VentasDia.

Después, se hace un SELECT a la tabla RDB$DATABASE. ¿Por qué a esa tabla? porque está garantizada de tener siempre una fila, solamente una fila, y nunca más de una fila. Aquí podrías usar cualquier otra tabla de la cual estás seguro de que siempre tendrá exactamente una fila, no es obligatorio usar RDB$DATABASE, puedes usar cualquier otra tabla que siempre tenga una fila.

Se selecciona la columna VENTAS de la tabla virtual VentasDia para cada día de la semana. ¿Cómo sabemos cuál es el día de la semana? Pues en ldFecIni tenemos el domingo. Si le sumamos 1 tendremos el lunes. Si le sumamos 2 tendremos el martes, y así sucesivamente.

Los alias a la tabla virtual los nombramos como D0, D1, D2, D3, etc. donde la letra «D» significa «día».

Finalmente, los valores de todas esas columnas los guardamos en los parámetros de salida ftnVentasDom, ftnVentasLun, etc.

Al ejecutar este stored procedure obtendremos algo como:

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

Las columnas que tienen cero implican que ese día no hubo ventas. Para ello se usó la función COALESCE() para que si el resultado de ejecutar la función SUM() era NULL lo convierta a cero.

En síntesis, este stored procedure recibe un parámetro de entrada (la fecha de un día de la semana que nos interesa) y devuelve siete parámetros de salida (el total de las ventas del domingo, el total de las ventas del lunes, el total de las ventas del martes, etc.)

Artículo relacionado:

El índice del blog Firebird21

Usando CTE (Common Table Expression)

Una de las excelentes construcciones que tiene Firebird se llama CTE (Common Table Expression) porque nos facilita grandemente escribir consultas complejas.

¿Qué es CTE?

Es una tabla virtual que se escribe antes del SELECT principal y que es eliminada cuando finaliza el SELECT principal. El SELECT principal puede referenciar a cualquier CTE como si fuera una tabla o una vista normal. Las CTE también pueden ser recursivas (o sea que pueden llamarse a sí mismas), pero no pueden ser anidadas (o sea que no puede existir una CTE dentro de otra CTE).

¿Cómo se escribe una CTE?

WITH NombreCTE AS (
   SELECT .....
)

Fíjate que se usan paréntesis para indicar donde empieza y donde termina el SELECT, no se usa BEGIN … END

¿Cuándo es conveniente usar CTE?

Cuando para escribir nuestro SELECT principal necesitamos columnas de otra tabla que cumplen una condición o que necesitamos tenerlas agrupadas o necesitamos unir a dos o más tablas o vistas o stored procedures seleccionables.

Eso implica que escribir una CTE como la siguiente es un error porque no te otorga algún beneficio:

WITH NombresClientes AS (
   SELECT CLI_NOMBRE FROM CLIENTES
)

¿Por qué es un error escribir una CTE como la de arriba? porque en el SELECT no se usó la cláusula WHERE ni la cláusula GROUP BY ni una función agregada ni el comando UNION, por lo tanto nada se ha ganado al escribirla, fue un desperdicio de tiempo y se escribió más de lo necesario.

Ejemplo:

Queremos mostrar, para cada sucursal de la Empresa, el total de las ventas del año 2011 y el total de las ventas del año 2012.

Para ello escribimos una vista que hace uso de una CTE. Nuestra tabla MOVIMCAB tiene estos datos:

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

CREATE VIEW V_TOTAL_VENTAS(
   SUC_CODIGO,
   VENTAS2011,
   VENTAS2012)
AS

   WITH TotalVentasAnuales AS (
      SELECT
         MVC_CODSUC,
         EXTRACT(YEAR FROM MVC_FECHAX) AS Ano,
         SUM(MVC_TOTALX * MVC_COTIZA) AS VentasAnuales
      FROM
         MOVIMCAB
      GROUP BY
         1, 2
   )

   SELECT
      S.SUC_CODIGO,
      V2011.VENTASANUALES AS VENTAS2011,
      V2012.VENTASANUALES AS VENTAS2012
   FROM
      SUCURSALES S
   LEFT JOIN
      TotalVentasAnuales V2011
         ON S.SUC_CODIGO = V2011.MVC_CODSUC AND
            V2011.Ano = 2011
   LEFT JOIN
      TotalVentasAnuales V2012
         ON S.SUC_CODIGO = V2012.MVC_CODSUC AND
            V2012.Ano = 2012;

Y este es el resultado que obtenemos:

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

En el SELECT principal se usó a la CTE llamada TotalVentasAnuales como si fuera una tabla normal. Se usó LEFT JOIN y no simplemente JOIN porque alguna Sucursal puede no tener ventas de un año (porque se cerró la Sucursal antes de ese año o porque se la abrió después de ese año).

Por supuesto que no es la única forma de llegar a ese resultado pero la ventaja de usar CTE es que nuestro código es mucho más entendible y por lo tanto si hay algún error será mucho más fácil encontrarlo y corregirlo.

Conclusión:

Sería muy conveniente que te acostumbres a usar CTE cuando en tu consulta necesitas datos de otra tabla (para hacer un JOIN) y los datos de esa otra tabla deben encontrarse en columnas agrupadas o requieren de funciones agregadas o del comando UNION porque más rápidamente escribirás tu consulta.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21