Saturday November 18th 2017

Categories

Insider

Archives

Derived Table with – WITH Clause

sqlI have included the overview of Derived Table with WITH Clause as they have an important application in Data Base Reporting. A dervied table built with a WITH clause is also called “Common Table Expression” can be thought of a table within SQL statement that exists only for the duration of that statement. There can be many CTEs in a SQL statement. Each must have a unique name and be declared at front of single query using WITH Clause. With Clause is new to SQL-99. Source Joe Celko’s SQL for Smarties Third Edition. Derived tables can be built in the from clause or in a with Recursive clause. The latter is new to SQL 99. Essentially a derived table is like an inline view created by the programmer instead of administrator. The with clause allow the query to use the same result set in several places, which was not possible with original derived table. See below the example;
With Itemsummary(UPC, Price_tot)
As select upc, sum(price) from orderdetails group by UPC)
Select p1.upc, Sum(price) from itemsummary as P1
Where P1.UPC, P1.Price_tot from Itemsummary as p1
Where P1.price_tot
= (Select Max(P1.price_tot) from
From ItemSummary as P2)