文档首页> 数据仓库服务 GaussDB(DWS)> 工具指南（阿布扎比区域）> DSC SQL语法迁移工具> DB2语法迁移> DML（DB2）
更新时间：2022-06-13 GMT+08:00
查看PDF

DML（DB2）

SELECT语句

FETCH子句

DB2语法

迁移后语法

 
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;

fetch-first子句设置了可检索的最大行数。

WITH AS

WITH AS带列列表

DB2语法

迁移后语法

 
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子句用于显示指定AS的属性值。

DB2语法

迁移后语法

 
 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函数由子查询指定。

DB2语法

迁移后语法

 
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语法迁移