DB2 common table expression usage

The DB2 public table expression is a temporary table. The following describes how to use the DB2 public table expression. We hope that you can have a deeper understanding of the DB2 public table expression.

DB2 uses public table expressions

The DB2 common table expression is a local temporary table that can be referenced multiple times in an SQL statement. This temporary table can only exist within the lifecycle of the SQL statement that defines it. Each time a public table expression is referenced, the results are the same. Temporary tables are defined using the WITH clause in SQL statements. The specific syntax is as follows:

WITH <COMMON NAME1> AS (<select expression>), <COMMON NAME2>

AS (<select expression), & SELECT <COLUMN> FROM <TABLE_NAME> <WHERE_CLAUSE>
 
<Table_name> is a table in the database. It can also be defined by an SQL statement containing the WITH clause. <Common name>. The following is an example:

 
 

  
  
WITH PROD_QUANTITY AS  
  
  
 
  
  
(SELECT PRODUCT_ID, SUM (QUANTITY) AS QUANTITY  
  
  
 
  
  
    FROM CUSTOMER_ORDER_ITEM  
  
  
 
  
  
    GROUP BY PRODUCT_ID),  
  
  
 
  
  
 
  
  
TOTALS AS  
  
  
 
  
  
(SELECT -1 AS PRODUCT_ID, SUM(QUANTITY) AS TOTAL)  
  
  
 
  
  
 
  
  
SELECT PRODUCT_ID, QUANTITY   
  
  
 
  
  
FROM PROD_QUANTITY  
  
  
 
  
  
UNION  
  
  
 
  
  
SELECT PRODUCT_ID, TOTALS  
  
  
 
  
  
FROM TOTALS  
  
  
 
  
  
ORDER BY 1 DESC  
 
 

In the preceding example, prod_quantity is defined as a common table expression. It is used with a public table expression named totals. The final SELECT statement will be selected from two common table expressions.

The following is another example:

 
 

  
  
WITH  
  
  
 
  
  
PAYLEVEL AS                                                   
  
  
 
  
  
    (SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,             
  
  
 
  
  
        SALARY+BONUS+COMM AS TOTAL_PAY                                       
  
  
 
  
  
        FROM EMPLOYEE                                                           
  
  
 
  
  
        WHERE EDLEVEL > 16),                                                         
  
  
 
  
  
 
  
  
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS           
  
  
 
  
  
    (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)                    
  
  
 
  
  
         FROM PAYLEVEL                                              
  
  
 
  
  
         GROUP BY EDLEVEL, HIREYEAR)  
  
  
 
  
  
 
  
  
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)  
  
  
 
  
  
    FROM PAYLEVEL, PAYBYED                                               
  
  
 
  
  
    WHERE EDLEVEL = EDUC_LEVEL                                          
  
  
 
  
  
        AND HIREYEAR= YEAR_OF_HIRE                                
  
  
 
  
  
        AND TOTAL_PAY < AVG_TOTAL_PAY       
 
 

This public table expression contains the PAYLEVEL. The result table includes the employee ID, the year of the employee's employment, the total salary of the employee, and the education level of the employee. Only records of employees with education levels greater than 16 are required.

The List also contains a public table expression called PAYBYED "pay by education. It uses the PAYLEVEL table to determine the educational level, the year of employment, and the average salary of employees with the same educational level as the same year. The column names obtained from this table, such as EDUC_LEVEL, are different from the column names used in the selection list.

Finally, we get the actual query that can generate the required results. This query connects two tables PAYLEVEL and PAYBYED) to determine the employees whose salaries are lower than the average salaries of all employees employed in the same year. Note that PAYBYED is based on PAYLEVEL, so in the entire statement, PAYLEVEL is actually accessed twice. During the two queries, the same group of rows were used.

After defining a public table expression, you can use it in an SQL statement just like using other tables. Public table expressions can be used at any time. You can create a public table expression based on the previously created public table expression.

Cross-database access to DB2 tables

Deep understanding of DB2 stored procedures

DB2 exception Processor type

DB2 connection Server Configuration

Learn about the DB2 lock types