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.
Valentina 2.5. This feature is supported in this release of Valentina and higher.