文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> DB2语法迁移> DML

DML

分享
更新时间:2020/08/05 GMT+08:00

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 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;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!非常感谢您的反馈,我们会继续努力做到更好!
反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问