Help Center/ GaussDB(DWS)/ More Documents/ Tool Guide (Paris Region)/ DSC: SQL Syntax Migration Tool/ DB2 Syntax Migration/ DML
Updated on 2022-07-29 GMT+08:00
View PDF
Share

DML

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;

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;

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;

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;
Parent topic: DB2 Syntax Migration