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

DML

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

本节主要介绍Teradata DML的迁移语法。迁移语法决定了关键字/特性的迁移方式。

在Teradata中,如果某文件中包含SELECT、INSERT、UPDATE、DELETE和MERGE语句,则该文件中的SQL查询可迁移到GaussDB T、GaussDB A 和 DWS。

详见以下节点内容:

INSERT

SELECT

UPDATE

DELETE

MERGE

NAMED

ACTIVITYCOUNT

TIMESTAMP

INSERT

Teradata的INSERT(缩写关键字为INS)语句用于向表中插入记录。DSC支持INSERT语句。

Teradata SQL中存在INSERT INTO TABLE table_name语法,但GaussDB A不支持。GaussDB A仅支持INSERT INTO table_name。DSC工具需要去除关键词TABLE。

输入

INSERT TABLE tab1 
SELECT col1, col2 
  FROM tab2 
 WHERE col3 > 0;

输出

INSERT INTO tab1 
SELECT col1, col2 
  FROM tab2 
 WHERE col3 > 0;

SELECT

  1. ANALYZE

    Teradata的SELECT命令(缩写关键字为SEL)用于指定从哪一列中检索数据。

    在GaussDB T、GaussDB A 和 DWS中使用ANALYZE来收集优化器统计信息,这些统计信息将用于查询性能。

    输入:ANALYZE,使用INSERT

    1
    2
    INSERT INTO employee(empno,ename)  Values (1,'John');
    COLLECT STAT on employee;
    

    输出

    1
    2
    3
    INSERT INTO employee( empno, ename)                                            
    SELECT 1 ,'John';
    ANALYZE employee;
    

    输入:ANALYZE,使用UPDATE

    1
    2
    3
    UPD employee SET ename = 'Jane'
            WHERE ename = 'John';
    COLLECT STAT on employee;
    

    输出

    1
    2
    3
    UPDATE employee SET ename = 'Jane'
     WHERE ename = 'John';
    ANALYZE employee;
    

    输入:ANALYZE,使用DELETE

    1
    2
    DEL FROM employee WHERE ID > 10;
    COLLECT STAT on employee;
    

    输出

    1
    2
    DELETE FROM employee WHERE ID > 10;
    ANALYZE employee;
    
  2. 子句顺序

    从Teradata迁移SELECT语句时,各子句(FROM、WHERE、HAVING和GROUP BY)可按任意顺序排列。如果语句的FROM子句之前包含作为ALIAS的QUALIFY子句,则DSC不会迁移该语句。

    可以使用tdMigrateALIAS参数来配置ALIAS的迁移。

    输入:子句顺序

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    SELECT expr1 AS alias1
          , expr2 AS alias2 
          , expr3 AS alias3
          , MAX( expr4 ), ...
       FROM tab1 T1 INNER JOIN tab2 T2
         ON T1.c1 = T2.c2 ...
        AND T3.c5 = '010'
        AND ...
      WHERE T1.c7 = '000'
        AND ...
     HAVING alias1 <> 'IC'
             AND alias2 <> 'IC'
             AND alias3 <> ''
      GROUP BY 1, 2, 3 ;
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    SELECT expr1 AS alias1
         , expr2 AS alias2
         , expr3 AS alias3
         , MAX( expr4 ), ...
      FROM tab1 T1 INNER JOIN tab2 T2
        ON T1.c1 = T2.c2 ...
       AND T3.c5 = '010'
       AND ...
     WHERE T1.c7 = '000'
       AND ...
     GROUP BY 1 ,2 ,3
    HAVING expr1 <> 'IC'
            AND expr2 <> 'IC'
            AND expr3 <> '';
    

    输入:子句顺序

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
              TOP 10 *
         GROUP BY
              DeptNo
         WHERE
              empID < 100
    FROM
              tbl_employee;
    

    输出

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT
              *
         FROM
              tbl_employee
         WHERE
              empID < 100
         GROUP BY
              DeptNo LIMIT 10
    ;
    

    如果输入脚本的FROM子句之前包含作为ALIAS的QUALIFY子句,DSC将不会迁移该语句,也不会逐字复制输入的语句。

    输入:子句顺序,在FROM子句之前使用QUALIFY作为ALIAS

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    SELECT
              *
         FROM
              table1
         WHERE
              abc = (
                   SELECT
                             col1 AS qualify
                        FROM
                             TABLE
                             WHERE
                                  col1 = 5
              )
    ;
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    SELECT
              *
         FROM
              table1
         WHERE
              abc = (
                   SELECT
                             col1 AS qualify
                        FROM
                             TABLE
                             WHERE
                                  col1 = 5
              )
    ;
    
  3. 扩展Group By子句

    如果用户希望数据库根据expr(s)的值对选定的行进行分组,则可指定GROUP BY子句。 如果此子句包含CUBE,ROLLUP或GROUPING SETS扩展,则除了常规分组之外,数据库还会生成超级聚合分组。这些特性在GaussDB T、GaussDB A 和 DWS中不可用,使用UNION ALL操作符可以实现类似的功能。

    可以使用extendedGroupByClause参数来配置扩展GROUP BY子句的迁移。

    输入:扩展Group By子句,使用CUBE

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    SELECT expr1 AS alias1
          , expr2 AS alias2 
          , expr3 AS alias3
          , MAX( expr4 ), ...
       FROM tab1 T1 INNER JOIN tab2 T2
         ON T1.c1 = T2.c2 ...
        AND T3.c5 = '010'
        AND ...
      WHERE T1.c7 = '000'
        AND ...
     HAVING alias1 <> 'IC'
             AND alias2 <> 'IC'
             AND alias3 <> ''
      GROUP BY 1, 2, 3 ;
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    SELECT expr1 AS alias1
         , expr2 AS alias2
         , expr3 AS alias3
         , MAX( expr4 ), ...
      FROM tab1 T1 INNER JOIN tab2 T2
        ON T1.c1 = T2.c2 ...
       AND T3.c5 = '010'
       AND ...
     WHERE T1.c7 = '000'
       AND ...
     GROUP BY 1 ,2 ,3
    HAVING expr1 <> 'IC'
            AND expr2 <> 'IC'
            AND expr3 <> '';
    

    输入:扩展Group By子句,使用ROLLUP

    1
    2
    3
    4
    5
    SELECT d.dname, e.job, MAX(e.sal)
      FROM emp e RIGHT OUTER JOIN dept d
        ON e.deptno=d.deptno
    WHERE e.job IS NOT NULL
    GROUP BY ROLLUP (d.dname, e.job);
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    SELECT dname, job, ColumnAlias1
      FROM ( SELECT MAX(e.sal) AS ColumnAlias1, d.dname, e.job
               FROM emp e RIGHT OUTER JOIN dept d
                 ON e.deptno = d.deptno
              WHERE e.job IS NOT NULL
              GROUP BY d.dname ,e.job
              UNION ALL 
             SELECT MAX(e.sal) AS ColumnAlias1, d.dname, NULL AS                    
                     job
               FROM emp e RIGHT OUTER JOIN dept d
                 ON e.deptno = d.deptno
              WHERE e.job IS NOT NULL
              GROUP BY d.dname
              UNION ALL 
             SELECT MAX( e.sal ) AS ColumnAlias1, NULL AS dname,  
                         NULL AS job
               FROM emp e RIGHT OUTER JOIN dept d
                 ON e.deptno = d.deptno
              WHERE e.job IS NOT NULL
            );
    

    输入:扩展Group By子句,使用GROUPING SETS

    1
    2
    3
    4
    5
    SELECT d.dname, e.job, MAX(e.sal)
    FROM emp e RIGHT OUTER JOIN dept d
    ON e.deptno=d.deptno
    WHERE e.job IS NOT NULL
    GROUP BY GROUPING SETS(d.dname, e.job);
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    SELECT dname, job, ColumnAlias1
      FROM ( SELECT MAX(e.sal) AS ColumnAlias1
                  , d.dname, NULL AS job
               FROM emp e RIGHT OUTER JOIN dept d
                 ON e.deptno = d.deptno
              WHERE e.job IS NOT NULL
              GROUP BY d.dname
              UNION ALL 
             SELECT MAX(e.sal) AS ColumnAlias1
                  , NULL AS dname, e.job
               FROM emp e RIGHT OUTER JOIN dept d
                 ON e.deptno = d.deptno
              WHERE e.job IS NOT NULL
              GROUP BY e.job
            );
    
  4. TOP和SAMPLE子句

    Teradata的TOP和SAMPLE子句在GaussDB T、GaussDB A 和 DWS中迁移为LIMIT。

    1. TOP

      DSC还支持迁移使用动态参数的TOP语句。

      • 对于包含WITH TIES的TOP语句,需要指定ORDER BY子句,否则工具不会迁移该语句,只会原样复制。
      • 使用TOP和动态参数时:
        • 按照以下形式输入动态参数:
          1
           TOP :<parameter_name>
          

          可使用的字符包括:小写英文字母(a-z)、大写英文字母(A-Z)、数字(0-9)、下划线(_)

      输入:SELECT...TOP

      1
      2
      3
      4
      SELECT TOP 1 c1, COUNT (*) cnt 
        FROM tab1 
       GROUP BY c1 
       ORDER BY cnt;	
      

      输出

      1
      2
      3
      4
      5
      SELECT c1, COUNT( * ) cnt
        FROM tab1
       GROUP BY c1
       ORDER BY cnt 
       LIMIT 1;
      

      输入:SELECT...TOP PERCENT

      1
      2
      3
      4
      SELECT TOP 10 PERCENT c1, c2 
        FROM employee
       WHERE ...
       ORDER BY c2 DESC;
      

      输出

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      WITH top_percent AS (
            SELECT c1, c2
              FROM employee
             WHERE ...
             ORDER BY c2 DESC
                          ) 
      SELECT *
        FROM top_percent 
       LIMIT (SELECT CEIL(COUNT( * ) * 10 / 100)
                FROM top_percent);
      

      输入:SELECT...TOP,使用动态参数

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      SELECT
                 TOP :Limit WITH TIES c1
                ,SUM (c2) sc2
           FROM
                tab1
           WHERE
                c3 > 10
           GROUP BY
                c1
           ORDER BY
                c1
      ;
      

      输出

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      WITH top_ties AS (
           SELECT
                      c1
                     ,SUM (c2) sc2
                     ,rank (
                     ) OVER( ORDER BY c1 ) AS TOP_RNK
                FROM
                     tab1
                WHERE
                     c3 > 10
                GROUP BY
                     c1
      ) SELECT
                c1
                ,sc2
           FROM
                top_ties
           WHERE
                TOP_RNK <= :Limit
           ORDER BY
                TOP_RNK
      ;
      

      输入:SELECT...TOP,使用动态参数和TIES

      1
      2
      3
      4
      5
      6
      7
       SELECT
                 TOP :Limit WITH TIES Customer_ID
         FROM
                Customer_t
         ORDER BY
                Customer_ID
      ;
      

      输出

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      WITH top_ties AS (
           SELECT
                     Customer_ID
                     ,rank (
                     ) OVER( order by Customer_id) AS TOP_RNK
                FROM
                     Customer_t
      ) SELECT
                Customer_ID
           FROM
                top_ties
           WHERE
                TOP_RNK <= :Limit
           ORDER BY
                TOP_RNK
      ;
      

      输入:SELECT...TOP PERCENT,使用动态参数

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      SELECT
                TOP :Input_Limit PERCENT WITH TIES c1
                ,SUM (c2) sc2
           FROM
                tab1
           GROUP BY
                c1
           ORDER BY
                c1
      ;
      

      输出

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      WITH top_percent_ties AS (
           SELECT
                     c1
                     ,SUM (c2) sc2
                     ,rank (
                     ) OVER( ORDER BY c1 ) AS TOP_RNK
                FROM
                     tab1
                GROUP BY
                     c1
      ) SELECT
                c1
                ,sc2
           FROM
                top_percent_ties
           WHERE
                TOP_RNK <= (
                     SELECT
                               CEIL(COUNT( * ) * :Input_Limit / 100)
                          FROM
                               top_percent_ties
                )
           ORDER BY
                TOP_RNK
      ;
      
    2. SAMPLE

      工具仅支持在SAMPLE子句中使用单个正整数。

      输入:SELECT...SAMPLE

      1
      2
      3
      4
      SELECT c1, c2, c3 
        FROM tab1 
       WHERE c1 > 1000
      SAMPLE 1;
      

      输出

      1
      2
      3
      4
      SELECT c1, c2, c3
        FROM tab1
       WHERE c1 > 1000
       LIMIT 1;
      

UPDATE

该工具支持和迁移UPDATE语句(缩写关键字为UPD)。

输入:UPDATE,使用TABLE ALIAS

1
2
3
4
5
UPDATE T1
  FROM tab1 T1, tab2 T2
   SET c1 = T2.c1                
     , c2 = T2.c2 
 WHERE T1.c3 = T2.c3;

输出

1
2
3
4
5
UPDATE tab1 T1
   SET c1 = T2.c1                
     , c2 = T2.c2 
  FROM tab2 T2
 WHERE T1.c3 = T2.c3;

输入:UPDATE,使用TABLE ALIAS和子查询

1
2
3
4
5
UPDATE t1
  FROM tab1 t1, ( SELECT c1, c2 FROM tab2
                   WHERE c2 > 100 ) t2
   SET c1 = t2.c1
 WHERE t1.c2 = t2.c2;

输出

1
2
3
4
5
 UPDATE tab1 t1
   SET c1 = t2.c1
  FROM ( SELECT c1, c2 FROM tab2
          WHERE c2 > 100 ) t2
 WHERE t1.c2 = t2.c2;

输入:UPDATE,使用ANALYZE

1
2
3
UPD employee SET ename = 'Jane'
        WHERE ename = 'John';
COLLECT STAT on employee;

输出

1
2
3
UPDATE employee SET ename = 'Jane'
 WHERE ename = 'John';
ANALYZE employee;

DELETE

DELETE(缩写关键字为DEL)是ANSI标准的SQL语法操作符,用于从表中删除记录。DSC支持Teradata的DELETE语句及其缩写关键字DEL。不包含WHERE子句的DELETE语句在GaussDB T、GaussDB A 和 DWS中被迁移为TRUNCATE。通过deleteToTruncate参数可以配置是否启用/禁用此行为。

输入:DELETE

1
2
DEL FROM tab1
 WHERE a =10;

输出

1
2
DELETE FROM tab1
 WHERE a =10;

输入:DELETE,不使用WHERE(如果deletetoTruncate=TRUE,则迁移为TRUNCATE)

1
DELETE FROM ${schemaname} . "tablename" ALL;

输出

1
2
3
TRUNCATE
     TABLE
          ${schemaname} . "tablename";

以下输入示例中,DELETE和FROM子句引用相同表,按是否使用WHERE子句区分:

输入

DELETE DP_TMP.M_P_TX_SCV_REMAINING_PARTY 
FROM DP_TMP.M_P_TX_SCV_REMAINING_PARTY ALL ;
---
DELETE DP_VMCTLFW.CTLFW_Process_Id 
FROM DP_VMCTLFW.CTLFW_Process_Id 
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );
---
DELETE CPID 
FROM DP_VMCTLFW.CTLFW_Process_Id AS CPID
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );

输出

DELETE FROM DP_TMP.M_P_TX_SCV_REMAINING_PARTY;
---
DELETE FROM DP_VMCTLFW.CTLFW_Process_Id 
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );
---
DELETE FROM DP_VMCTLFW.CTLFW_Process_Id AS CPID
WHERE (Process_Name =  :_spVV2 ) 
AND  (Process_Id  NOT IN (SELECT MAX(Process_Id )(NAMED Process_Id ) 
                                      FROM DP_VMCTLFW.CTLFW_Process_Id 
                                     WHERE Process_Name =  :_spVV2 )
      );

DELETE table_alias FROM table

输入

SQL_Detail10124.sql
delete a
  from ${BRTL_DCOR}.BRTL_CS_POT_CUST_UMPAY_INF_S as a
 where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') 
 and a.DW_Job_Seq = 1 ;
was migrated as below:
      DELETE FROM
           BRTL_DCOR.BRTL_CS_POT_CUST_UMPAY_INF_S AS a
                USING
      WHERE a.DW_Snsh_Dt = CAST( lv_mig_v_Trx_Dt AS DATE )
           AND a.DW_Job_Seq = 1 ;
SQL_Detail10449.sql
delete a
  from ${BRTL_DCOR}.BRTL_EM_YISHITONG_USR_INF as a
 where a.DW_Job_Seq = 1 ;
was migrated as below:
      DELETE FROM
           BRTL_DCOR.BRTL_EM_YISHITONG_USR_INF AS a
                USING
      WHERE a.DW_Job_Seq = 1 ;
SQL_Detail5742.sql
delete a
  from ${BRTL_DCOR}.BRTL_PD_FP_NAV_ADT_INF as a;
was migrated as 
      DELETE a
 FROM
      BRTL_DCOR.BRTL_PD_FP_NAV_ADT_INF AS a ;

输出

SQL_Detail10124.sql
delete from ${BRTL_DCOR}.BRTL_CS_POT_CUST_UMPAY_INF_S as a
 where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') 
 and a.DW_Job_Seq = 1 ;
SQL_Detail10449.sql
delete from ${BRTL_DCOR}.BRTL_EM_YISHITONG_USR_INF as a
 where a.DW_Job_Seq = 1 ;
SQL_Detail5742.sql
delete from ${BRTL_DCOR}.BRTL_PD_FP_NAV_ADT_INF as a;

MERGE

6.5.0及之后版本的GaussDB支持MERGE功能。

MERGE是ANSI标准的SQL语法操作符,用于从一个或多个来源中选择行来更新或插入到表或视图中,可以指定更新或插入到目标表或视图的条件。

输入:MERGE

1
2
3
4
5
6
7
8
MERGE INTO tab1 A
using ( SELECT c1, c2, ... FROM tab2 WHERE ...) AS B
ON A.c1 = B.c1
 WHEN MATCHED THEN 
   UPDATE SET c2 = c2
            , c3 = c3
  WHEN NOT MATCHED THEN 
INSERT VALUES (B.c1, B.c2, B.c3);

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
WITH B AS (
     SELECT
               c1
               ,c2
               ,...
          FROM
               tab2
          WHERE
               ...
)
,UPD_REC AS (
     UPDATE
               tab1 A
          SET
               c2 = c2
               ,c3 = c3
          FROM
               B
          WHERE
               A.c1 = B.c1 returning A. *
)
INSERT
     INTO
          tab1 SELECT
                    B.c1
                    ,B.c2
                    ,B.c3
                 FROM
                    B
                WHERE
                    NOT EXISTS (
                         SELECT
                                 1
                           FROM
                                 UPD_REC A
                          WHERE
                                 A.c1 = B.c1
                               )
; 

NAMED

Teradata中的NAMED用于为表达式或列分配临时名称。用于表达式的NAMED语句在GaussDB T、GaussDB A 和 DWS中被迁移为AS。用于列名的NAMED语句保留在相同的语法中。

输入:NAMED表达式,迁移为AS

1
2
3
SELECT Name, ((Salary + (YrsExp * 200))/12) (NAMED Projection)
  FROM Employee
 WHERE DeptNo = 600 AND Projection < 2500;

输出

1
2
3
SELECT Name, ((Salary + (YrsExp * 200))/12) AS  Projection
  FROM Employee
 WHERE DeptNo = 600 AND ((Salary + (YrsExp * 200))/12)  < 2500;

输入:NAMED AS,定义列名

1
2
SELECT product_id AS id
  FROM emp where pid=2 or id=2;

输出

1
2
SELECT product_id (NAMED "pid") AS id
  FROM emp where product_id=2 or product_id=2;

输入:NAMED( ),定义列名

1
INSERT INTO Neg100 (NAMED,ID,Dept) VALUES ('TEST',1,'IT');

输出

1
INSERT INTO Neg100 (NAMED,ID,Dept) SELECT 'TEST',1, 'IT';

输入:NAMED别名,使用TITLE别名,不使用AS

1
2
3
SELECT dept_name (NAMED alias1) (TITLE alias2 ) 
  FROM employee 
 WHERE dept_name like 'Quality';

输出

1
2
3
4
SELECT dept_name 
    AS alias1 
  FROM employee
 WHERE dept_name like 'Quality';

输入:NAMED别名,使用TITLE别名和AS

DSC将跳过NAMED别名和TITLE别名,仅使用AS别名。

1
2
3
4
SELECT sale_name (Named alias1 ) (Title alias2) 
    AS alias3 
  FROM employee 
 WHERE sname = 'Stock' OR sname ='Sales';

输出

1
2
3
4
SELECT sale_name 
    AS alias3 
  FROM employee 
 WHERE sname = 'Stock' OR sname ='Sales';

输入:NAMED,使用TITLE

NAMED和TITLE一起使用,通过逗号隔开。

1
2
3
SELECT customer_id (NAMED cust_id, TITLE 'Customer Id')
FROM Customer_T 
WHERE cust_id > 10;

输出

1
2
3
4
SELECT cust_id AS "Customer Id" 
FROM   (SELECT customer_id AS cust_id 
                FROM   customer_t 
                WHERE  cust_id > 10); 

ACTIVITYCOUNT

输入

状态变量,返回嵌入式SQL中受DML语句影响的行数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SEL tablename
FROM dbc.tables
WHERE databasename ='tera_db'
  AND tablename='tab1';

.IF ACTIVITYCOUNT > 0 THEN .GOTO NXTREPORT;
CREATE MULTISET TABLE tera_db.tab1
        , NO FALLBACK
        , NO BEFORE JOURNAL
        , NO AFTER JOURNAL
        , CHECKSUM = DEFAULT
          (
                    Tx_Zone_Num CHAR( 4 )
                  , Tx_Org_Num  VARCHAR( 30 )
          )
          PRIMARY INDEX
          (
                    Tx_Org_Num
          )
          INDEX
          (
                    Tx_Teller_Id
          )
;

.LABEL NXTREPORT
DEL FROM tera_db.tab1;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DECLARE v_verify TEXT ;
v_no_data_found NUMBER ( 1 ) ;

BEGIN
     BEGIN
          v_no_data_found := 0 ;

          SELECT
                    mig_td_ext.vw_td_dbc_tables.tablename INTO v_verify
               FROM
                    mig_td_ext.vw_td_dbc_tables
               WHERE
                    mig_td_ext.vw_td_dbc_tables.schemaname = 'tera_db'
                    AND mig_td_ext.vw_td_dbc_tables.tablename = 'tab1' ;

               EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                    v_no_data_found := 1 ;

     END ;

     IF
          v_no_data_found = 1 THEN
               CREATE TABLE tera_db.tab1 (
                    Tx_Zone_Num CHAR( 4 )
                    ,Tx_Org_Num VARCHAR( 30 )
               ) DISTRIBUTE BY HASH ( Tx_Org_Num ) ;

     CREATE
          INDEX
               ON tera_db.tab1 ( Tx_Teller_Id ) ;

     END IF ;

     DELETE FROM
          tera_db.tab1 ;

END ;
/

TIMESTAMP

输入:TIMESTAMP,使用FORMAT

FORMAT短语设置特定TIME或TIMESTAMP列或值的格式。FORMAT短语会覆盖系统格式。

1
2
SELECT 'StartDTTM' as a
             ,CURRENT_TIMESTAMP (FORMAT 'HH:MI:SSBMMMBDD,BYYYY');

输出

1
2
SELECT 'StartDTTM' AS a 
             ,TO_CHAR( CURRENT_TIMESTAMP ,'HH:MI:SS MON DD, YYYY' ) ;

TIMESTAMP类型装换:

输入

COALESCE( a.Snd_Tm ,TIMESTAMP '0001-01-01 00:00:00' )
should be migrated as below:
COALESCE( a.Snd_Tm , CAST('0001-01-01 00:00:00' AS TIMESTAMP) )

输出

COALESCE( a.Snd_Tm , CAST('0001-01-01 00:00:00' AS TIMESTAMP) )
分享:

    相关文档

    相关产品

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

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

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

字符长度不能超过200

提交反馈 取消

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

智能客服提问云社区提问