Common Table Expressions CTE

Esta es una característica de SQL Server se vio por primera vez en la versión 2005 y debo confesar que no fue de mis favoritas hasta que encontré su potencial al momento en que ella se llama así misma (anidamiento), su  ventaja principal es ofrecer mayor legibilidad a las consulta y por supuesto una facilidad en el mantenimiento de las mismas. .

CTE me permite cargar a memoria una consulta o query, darle un nombre para utilizarlo inmediatamente dentro del mismo bach de instrucciones, no queda almacenado como lo hace una vista, función o procedimiento almacenado, es solo de uso temporal, dentro del bach (conjunto) de instrucciones SQL .

Ejemplo:

---CTE Common Table Expression WITH CTE_ORDENES as ( Select c.companyname, o.orderid,      o.orderdate, year(o.orderdate) as año,      month(o.orderdate) as mes      from customers c inner join orders o      on c.CustomerID=o.CustomerID)	CREACIÓN DEL CTE
Select companyname, orderid from CTE_ORDENES  where año=1997 and mes=6 go	USO DEL CTE

Explicación:

CHOOSE

La función CHOOSE en SQL Server se compone de un parámetro de entrada y una lista de valores, de acuerdo al valor que contenga el primer parámetro de entra, devuelve el elemento de la lista correspondiente. 

En el siguiente ejemplo se usa la función Month para extraer el mes de una fecha, para que de acuerdo al número de mes coloque el mes en letras, usaremos la función CHOOSE, agregando el listado de nombre de los meses en el orden correspondiente.

Select c.companyname, o.orderid, o.orderdate,

    CHOOSE(MONTH(o.[orderdate]), 'Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre') AS NombreMes

from customers as c inner join orders o

on c.CustomerID=o.customerid

Describamoslo un poco mas.

Resultado:

La Función CASE

La función CASE en SQL Server nos permite evaluar el valor que contiene un campo o variable y en base a este valor condicionar un resultado.

Por ejemplo supongamos que tenemos el campo Estado Civil pero hemos decidido guardar la información de forma numérica asignando “1” para Casado y “2” para Soltero.

Aunque esta información se guarde de esta manera al momento de ejecutar una consulta o solicitar un reporte no nos sirve que el campo se muestre con números 1 y 2, en este caso queremos que estos números sean sustituidos por casado y soltero.

Para esto sirve la función CASE, veamos el ejemplo:

Select Nombre, Apellido,

CASE  EstadoCivil

       WHEN 1 THEN 'Casado'        

       WHEN 2 THEN 'Soltero'

    ELSE 'No Definido' END AS EstadoCivilNew

from Persona

También se puede escribir de la siguiente forma, solo cambia el alias del campo creado con la función CASE colocándolo al inicio en vez de al final que en vez de ponerlo al final:

Select Nombre, Apellido,

EstadoCivilNew= CASE  EstadoCivil

       WHEN 1 THEN 'Casado'        

       WHEN 2 THEN 'Soltero'

    ELSE 'No Definido' END

from Persona

Mi ejemplo solo se muestra condiciones para dos valores pero es  posible agregar más, ahora si existiera un valor que no está definido,  es decir para mi ejemplo si tuviéramos el valor “3” que no está definido entonces ejecutaría el mensaje puesto en la condición ELSE.

Explicación gráfica de Inner JOIN

Explicación de instrucción INNER JOIN en SQL Server.