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:

CTE1

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:

CTE2

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