With cte: Tablas de expresión común (2) Correlativos

Continuando con el capítulo anterior With cte (1)

Vamos a ver como utilizar la figura de las tablas de expresión común, pero correlativas. O lo que es lo mismo, poder procesar comportamientos con conjuntos.

-- Code 1.0

WITH R1
     AS (SELECT 'ELEMENTO 1 TBL 1' AS COL1
	   UNION ALL
	   SELECT 'ELEMENTO 2 TBL 1'
	),
     R2
     AS (SELECT 'Elemento 2' AS COL2),
     R3
     AS (SELECT 'Elemento 3' AS COL3),
     R4
     AS (SELECT 'Elemento 4' AS COL4)
     SELECT *
     FROM R1
          CROSS JOIN R2
          CROSS JOIN R3
          CROSS JOIN R4;

Como puedes observar el primer ejemplo, no es muy significativo, aunque espero que si sea bastante claro.

Nota: Si te da un error la ejecución del código, pon antes de With un punto y coma

Un cte correlativo, es una conjunto de resultados temporal que vive en el ámbito definido por el cte, seguido de otro/s conjuntos temporales de iguales características.

El primer conjunto, obtiene los literales Elemento 1 Tbl1 y Elemento 2 Tbl1 en 1 columna y dos filas.

Los siguientes conjuntos se separan del cierre del paréntesis del primero con una coma, y luego se les da un alias de tabla segúido de AS y de un bloque que define la consulta.

Por ejemplo:

With cte AS ( Select …… )

, cte2 AS ( Select …..)

, cte3 AS (Select …..)

Select * from (aquí puedo referenciar a los cte, cte2, cte3

Tambíen un cte puede referenciar a un conjunto anterior.

-- Code 1.1

WITH R1
     AS (SELECT 'ELEMENTO 1 TBL 1' AS COL1
	   UNION ALL
	   SELECT 'ELEMENTO 2 TBL 1'
	),
     R2
     AS (SELECT *
	from 
	(Values ('Elemento 2') ) V(col2)
	)
     SELECT *
     FROM R1
          CROSS JOIN R2;

Esto también puede ser

-- Code 1.2

WITH R1
     AS (SELECT 'ELEMENTO 1 TBL 1' AS COL1
	   UNION ALL
	   SELECT 'ELEMENTO 2 TBL 1'
	),
     R2
     AS (SELECT *
	 from 
	 (Values ('Elemento 2') ) V(col2)
	 cross join R1 
	)
     SELECT *
     FROM R2;
          

Resultado idéntico, pero si observas, en el primer código se trabajan los dos ctes en la salida, y en el segundo, se hace la referencia desde el cte2 al cte1.

Otra posibilidad es definir los nombres de las columnas al lado del nombre del conjunto

-- Code 1.3

WITH R1(col1)
     AS (SELECT 'ELEMENTO 1 TBL 1'  as MICOL
	   UNION ALL
	   SELECT 'ELEMENTO 2 TBL 1'
	),
     R2 (col2)
     AS (SELECT 'Elemento 2'),
     R3 (col3)
     AS (SELECT 'Elemento 3'),
     R4 (col4)
     AS (SELECT 'Elemento 4')
     SELECT *
     FROM R1
          CROSS JOIN R2
          CROSS JOIN R3
          CROSS JOIN R4;

Observa como en la salida, prevalece el alias de columna que establecemos en la definición del cte, por encima del alias de columna del interior del mismo.

Y ahora continuamos con el ejemplo del artículo anterior.

-- Code 1.4

With cte as (
Select cliente, 
       Count(*) as numVeces
   from facturacion 
group by Cliente
), cte2 as (
select sum(totalneto) as neto
     , c.cliente
     , c.numVeces
from cte c 
    inner join Facturacion f 
on c.cliente = f.cliente
   group by c.cliente, c.numveces
)
select c.neto
     , c.entidad
     , c.numVeces  
 from cte2 c;

Este es un código simple, donde utilizamos la tabla de expresión común para obtener los clientes, con el número de documentos que facturan, y luego utilizamos un segundo conjunto llamado cte2, para relacionar la salida del primer conjunto y realizar otra operación sobre el mismo.

Si bien, esta claro que en este ejemplo se podría realizar de muchas otras maneras, es descriptivo sobre como se pueden utilizar los ctes correlativos.

Como en cualquier otro conjunto, se pueden realizar combinaciones left/right/full join o cross/outer apply, o relacionarse consigo mismo.

Una de las pocas normas, que nos exigen, es que las tablas de expresión común empiezan por punto y coma, bueno realmente, la instrucción anterior tiene que terminar en punto y coma, se define su conjunto interior despues de AS entre paréntesis, y que la salida, tiene que ser CRUD.

Por tanto la salida final de un cte, puede ser:

Ejemplo con Insert

-- Code 1.5

With r as (
....
) 
Insert into laTabla
Select * from r;

Ejemplo con Update

-- Code 1.6

With r as (
....
) 
Update t
   Set col = r.col
From r join laTabla t 
   on r.col=t.col;

También se puede realizar directamente una update sin mencionar la tabla, siempre y cuando en el conjunto r, la definición de las columnas sea explicita a una tabla. De manera que si haces una update sobre R, el motor sabe a que tabla te refieres.

Ejemplo con Delete

-- Code 1.7

With r as (
  ....
)
Delete From ...

Aquí hay muchas variantes. Borrar directamente los registros del cte. Borrar haciendo una relación directa con la tabla. Utilizar la sintaxis de extensión de borrado de Tsql Delete From tabla From tabla ….

Es un recurso muy interesante, y fácil de trabajar.

With cte As: Tablas de expresión común 1.

With cte As: Tablas de expresión común 3. Recursividad

With cte As: Tablas de expresión común 4. Ordenar

Imagen de Peter H en Pixabay

…..