SQL TABLE EXPRESSION

DB2 SQL TABLE EXPRESSION : COMMON TABLE EXPRESSION


COMMON TABLE EXPRESSION (CTE)

  1. Unlike NESTED TABLE EXPRESSION CTE is not specified in FROM clause of the query.
  2. But CTE is specified before the full-select in a SELECT statement using WITH keyword.
  3. A single query can contain multiple CTEs but keyword WITH is used only once.
  4. Inside query CTE table is treated as any other normal table.
  5. By default CTE table use column names from query it is drived from(Or system generated name in case of some SQL Funtion like SUM() is used) , But there is option to rename CTE table columns

WITH AVSAL AS
     (SELECT WORKDEPT, AVG(SALARY) as AVGSAL 
        FROM EMPLOYE  GROUP BY WORKDEPT)
SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL 
FROM AVSAL GROUP BY WORKDEPT;

In above code “WITH AVSAL AS (SELECT WORKDEPT, AVG(SALARY) as AVGSAL FROM EMPLOYE  GROUP BY WORKDEPT)” defines a CTE with name AVSAL means now there is a temporary table with name AVSAL available to this query. In next line “SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL FROM AVSAL GROUP BY DEPT” select statement is used to get data from CTE table AVSAL like any other table.

Rename columns for CTE
WITH AVSAL(DEPARTMENT,AVGSALARY) AS
(SELECT   WORKDEPT, AVG(SALARY) as AVGSAL  
FROM  EMPLOYE  GROUP BY WORKDEPT)
SELECT DEPARTMENT, MAX(AVGSALARY) AS MAXAVGSAL 
FROM AVSAL GROUP BY DEPARTMENT;

In above query “WITH AVSAL(DEPARTMENT,AVGSALARY) AS” renames CTE table AVSAL’s columns to DEPARTMENT and AVGSALARY.

Using Multiple CTEs in single Query

This is a sample of complex query where Multiple CTE tables are being used in combination with SQL JOINS. For multiple CTEs in single query there is only single WITH clause.

WITH Ctable1 AS
(SELECT C1Field1, 
row_number() over(partition by C1Field2) as rowNbr
FROM SampleTable1  WHERE C1Field5 = "XYZ" ), 
		
Ctable2 AS
(SELECT  SampleTable1.* , 
row_number() over(order by C2Field3 desc) as rowNbr
FROM SampleTable2 
      INNER JOIN SampleTable3 ON C2Field = SampleTable3.C3Field
WHERE C2Field5 <= "ABC")

SELECT * FROM Ctable1 t1 
       FULL JOIN Ctable2 t2 ON t1.C1Field1 = t2.C2Field1 
WHERE t1.rowNbr = 1 OR t2.rowNbr = 1

In Above two temporary tables Ctable1 and Ctable2. Ctable1 is created using simple select statement with OLAP function row_number. Ctable2 is created by joining two tables SampleTable2 and SampleTable3.

And finally "SELECT statement" which join Ctable1 and Ctable2 using FULL JOIN.


IBM i developer.

View Comments
There are currently no comments.