Expresión de Tabla Común o CTE en SQL

Una expresión de tabla común o CTE (common table expression) se puede considerar como un conjunto de resultados temporales, que se definen en el ámbito de la ejecución de una instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW.

Una CTE es como una tabla temporal que sólo dura el tiempo que dura la ejecución. Una CTE puede hacer referencia a sí misma, y se puede hacer referencia a ella varias veces en la misma consulta.

La estructura básica de una CTE es:

WITH nombre_consulta [(columna [,…n])] AS

(

   definición_consulta_CTE

)

La lista de las columnas resultantes ES opcional sólo si se proporcionan nombres distintivos en la definición de la consulta.

Vamos a ver toda esta teoría en la práctica. Vamos a partir de la BBDD queya hemos definido para el ejemplo de la agenda.

Suponed que queremos seleccionar un número determinado de registros, como por ejemplo los 2 primeros. Desgraciadamente en SQL/400 no tenemos la sentencia LIMIT para limitar el número de registros a mostrar, como ocurre en MySQL.

Una solución podría ser generar una tabla temporal que contuviera el número de registro (ojo, número de registro no número relativo). Una vez tuviéramos esa tabla, realizaríamos una selección para aquellos registros cuyo número de registro sea inferior o igual a 2.

Pues bien, la tabla temporal la crearemos con una CTE y el resultado lo obtendremos realizando una consulta sobre esa CTE.

Definiremos la CTE de la siguiente forma:

WITH R1 AS (                                      

     SELECT NOMBRE, APELLIDOS, DIRECCION, TELEFONO,

            ROWNUMBER() OVER() AS NUMREG          

     FROM   BJOSEM/FAGENDAP                       

)                                                 

SELECT * FROM R1 WHERE NUMREG <= 2                

En la CTE seleccionamos los campos del fichero FAGENDAP, además de su número de registro. Este número lo obtenemos con la OLAP ROWNUMBER, que nos devuelve el número de registro según el orden establecido en OVER(). Como no hemos establecido orden alguno, contará según vayan saliendo los resultados.

Una vez tenemos la CTE, realizamos una consulta para seleccionar los registros de la CTE cuyo número sea igual o inferior a 2.

Si pudiéramos “depurar” la consulta, veríamos que la ejecución inicial de la CTE sería la siguiente:

Una vez tenemos la consulta generada, con la condición de que NUMREG <= 2 seleccionados sólo 2 registros.

Esto es un ejemplo simple de una CTE, pero las CTE pueden ayudarnos a simplificar mucho nuestras consultas.

Espero les haya servido y no olviden visitar cobol-400.com

Un saludo.