Switch to: V9V8V7V6V5

WITH Statement, SQL

Complex SQL expressions can be tedious to read. The WITH statement is a Common Table Expression (CTE) which also recently debuted in Microsoft SQL Server.

Common Table Expressions are similar to views. They let you write a VIEW that exists temporarily - only for the duration of the query itself.

   --CTE used to simplify SQL expression
   WITH ProductSales(ProductID, OrderYear, TotalSales)
   AS (
      SELECT 
         det.productID, 
         YEAR(hdr.orderdate), 
         SUM(det.linetotal) 
      FROM 
         sales.salesorderdetail det
         JOIN sales.salesorderheader hdr 
   ON det.salesorderid = hdr.salesorderid 
      GROUP BY 
         det.productID, 
         YEAR(hdr.orderdate)   
   )
 
   -- get list of products who had more than $1M 
   -- in sales in 2004
   SELECT 
      sal.ProductID, 
      prd.Name, 
      sal.TotalSales
   FROM 
      ProductSales sal
      JOIN Production.Product prd ON sal.ProductID = prd.ProductID
   WHERE 
      TotalSales > 1000000
      AND OrderYear = 2004

Important: Valentina resolves WITH during SQL-tree transformations. Valentina joins the tree of both queries first and only then executes it.

Release Information

Valentina 2.5. This feature is supported in this release of Valentina and higher.