In DB2 SQL, Common Table Expression (CTE) simplifies query writing; it derives and use to get needed result. The first word in CTE is WITH. Here is complete story of fullselect.
CTE: Common Table Expression Best Example.
WITH DEPTOTAL (WORKDEPT, MAXSALARY) AS
(SELECT WORKDEPT, SUM(SALARY+COMM)
FROM DSN8910.EMP
GROUP BY WORKDEPT)
SELECT WORKDEPT FROM DEPTOTAL
WHERE MAXSALARY =
(SELECT MAX(MAXSALARY)
FROM DEPTOTAL);
3 Steps to Complete SQL Query.
WITH
first SELECT
second SELECT
DB2 Tough Interview Questions.
Useful for interviews and Projects.
$1.00
How this SQL Query Works.
In this example, the DEPTOTAL common table expression is established by calculating the sum of the salaries plus commissions for each department.
The WITH clause identifies the common table expression, followed by the name of the common table expression and the column names.
The fullselect within the parentheses following the WITH clause defines the data for the common table expression. This data can then be used in the remainder of the query.
Related Posts
