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

DML（Teradata）

查看PDF

DML（Teradata）

分享
更新时间：2021/03/09 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) )
父主题： Teradata语法迁移
分享：

    相关文档

    相关产品