DML

Updated on 2023-04-03 GMT+08:00

View PDF

SELECT

FETCH clause

DB2 Syntax	Syntax After Migration
SELECT empno, ename, deptno FROM emp_t ORDER BY salary FETCH FIRST ROW ONLY ----- SELECT empno, ename FROM emp_t WHERE deptno = 10 ORDER BY salary fetch first 2 rows only;	SELECT empno, ename, deptno FROM emp_t ORDER BY salary LIMIT 1; ----- SELECT empno, ename FROM emp_t WHERE deptno = 10 ORDER BY salary LIMIT 2;

DB2 Syntax

Syntax After Migration

SELECT empno, ename, deptno  
   FROM emp_t  
  ORDER BY salary     
  FETCH FIRST ROW ONLY 
 ----- 
 SELECT empno, ename  
   FROM emp_t  
  WHERE deptno = 10 
  ORDER BY salary  
 fetch first 2 rows only;

SELECT empno, ename, deptno  
   FROM emp_t  
  ORDER BY salary     
  LIMIT 1; 
 ----- 
 SELECT empno, ename  
   FROM emp_t  
  WHERE deptno = 10 
  ORDER BY salary  
 LIMIT 2;

NOTE:

The fetch-first-clause sets a maximum number of rows that can be retrieved.

WITH AS

WITH AS with column list

DB2 Syntax	Syntax After Migration
WITH rec (emp_no, emp_name, dept_name, dept_no) AS ( SELECT empno, ename, cast('admin' as varchar(90)), 100 AS deptno FROM emp_t ) SELECT * FROM rec;	WITH rec AS ( SELECT empno AS emp_no, ename AS emp_name, cast('admin' as varchar(90)) AS dept_name, 100 AS dept_no FROM emp_t ) SELECT * FROM rec;

DB2 Syntax

Syntax After Migration

WITH rec (emp_no, emp_name, dept_name, dept_no) AS
   ( SELECT empno, ename, cast('admin' as varchar(90)), 100 AS deptno
       FROM emp_t )
SELECT * FROM rec;

WITH rec AS
   ( SELECT empno AS emp_no, ename AS emp_name, cast('admin' as varchar(90)) AS dept_name, 100 AS dept_no
       FROM emp_t )
SELECT * FROM rec;

WITH AS with VALUES clause

DB2 Syntax	Syntax After Migration
WITH rec (baseschema, basename, baselevel) AS ( VALUES( cast('SCOTT' as varchar(90)), cast('EMP' as varchar(90)), 10000 ) ) SELECT owner, table_name, baselevel -1 FROM ALL_TABLES, REC WHERE owner = BASESCHEMA AND table_name = BASENAME;	WITH rec AS ( SELECT cast('SCOTT' as varchar(90)) AS baseschema, cast('EMP' as varchar(90)) AS basename, 10000 AS baselevel From dual ) SELECT owner, table_name, baselevel -1 FROM ALL_TABLES, REC WHERE owner = BASESCHEMA AND table_name = BASENAME;

DB2 Syntax

Syntax After Migration

 WITH rec (baseschema, basename, baselevel) AS 
    ( VALUES( cast('SCOTT' as varchar(90)), cast('EMP' as varchar(90)), 10000 ) )   
   SELECT owner, table_name, baselevel -1    
     FROM ALL_TABLES, REC    
    WHERE owner = BASESCHEMA 
      AND table_name   = BASENAME;

 WITH rec AS 
    ( SELECT cast('SCOTT' as varchar(90)) AS baseschema, cast('EMP' as varchar(90)) AS basename, 10000 AS baselevel
From dual )   
   SELECT owner, table_name, baselevel -1    
     FROM ALL_TABLES, REC    
    WHERE owner       = BASESCHEMA 
      AND table_name  = BASENAME;

Table Function

TABLE function is specified with subquery.

DB2 Syntax	Syntax After Migration
SELECT prod_code, prod_desc, (received_qty-issued_qty) stk FROM prod p, TABLE(select prod_code, SUM(received_qty) received_qty FROM prod_recd) r , TABLE(select prod_code, SUM(issued_qty) issued_qty FROM prod_issue) i WHERE r.prod_code = p.prod_code AND i.prod_code = p.prod_code;	SELECT prod_code, prod_desc, (received_qty-issued_qty) stk FROM prod p, (select prod_code, SUM(received_qty) received_qty FROM prod_recd) r , (select prod_code, SUM(issued_qty) issued_qty FROM prod_issue) i WHERE r.prod_code = p.prod_code AND i.prod_code = p.prod_code;

DB2 Syntax

Syntax After Migration

SELECT prod_code, prod_desc, (received_qty-issued_qty) stk
  FROM prod p, TABLE(select prod_code, SUM(received_qty) received_qty FROM prod_recd) r
     , TABLE(select prod_code, SUM(issued_qty) issued_qty FROM prod_issue) i
 WHERE r.prod_code = p.prod_code
   AND i.prod_code = p.prod_code;

SELECT prod_code, prod_desc, (received_qty-issued_qty) stk
  FROM prod p, (select prod_code, SUM(received_qty) received_qty FROM prod_recd) r
     , (select prod_code, SUM(issued_qty) issued_qty FROM prod_issue) i
 WHERE r.prod_code = p.prod_code
   AND i.prod_code = p.prod_code;

Parent topic: DB2 Syntax Migration

Previous topic: Tables

Next topic: Index