Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> DB2 Syntax Migration> DML
View PDF

DML

SELECT

FETCH clause

DB2 Syntax

Syntac 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;

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

WITH AS

WITH AS with column list

DB2 Syntax

Syntac 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

Syntac 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

Syntac 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