更新时间:2024-06-29 GMT+08:00

TITLE

Teradata Permanent、Global Temporary和Volatile表支持关键字TITLE。在迁移过程中,TITLE文本将被注释掉。

如果TITLE文本拆分为多行,则在迁移后脚本中,换行符(ENTER)替换为空格。

输入:CREATE TABLE,使用TITLE

1
2
3
CREATE TABLE tab1 (
  c1  NUMBER(2) TITLE 'column_a'
);

输出

1
2
3
CREATE TABLE tab1 (
  c1  NUMBER(2) /* TITLE 'column_a' */
);

输入:TABLE,使用多行TITLE

1
2
3
4
CREATE TABLE tab1 (
  c1  NUMBER(2) TITLE 'This is a
very long title'
);

输出

1
2
3
CREATE TABLE tab1 (
  c1  NUMBER(2) /* TITLE 'This is a  very long title' */
);

输入:TABLE,使用列TITLE

DSC将列TITLE迁移为新的外部查询。

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

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT
          customer_id  AS "cust_id"
     FROM
          (
               SELECT
                         customer_id
                    FROM
                         Customer_T
                    WHERE
                         cust_id > 10
          )
;

输入:TABLE,使用列TITLE和QUALIFY

1
2
3
4
5
SELECT ord_id 
(TITLE 'Order_Id'), order_date, customer_id
  FROM order_t
WHERE Order_Id > 100
QUALIFY ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) <= 5;

输出

SELECT
          "mig_tmp_alias1" AS "Order_Id"
     FROM
          (
               SELECT
                         ord_id AS "mig_tmp_alias1"
                         ,ROW_NUMBER( ) OVER( PARTITION BY customer_id ORDER BY order_date DESC ) AS ROW_NUM1
                    FROM
                         order_t
                    WHERE
                         Order_Id > 100
          ) Q1
     WHERE
          Q1.ROW_NUM1 <= 5
;

TITLE和ALIAS

如果使用TITLE并指定ALIAS,则工具将按如下方式进行迁移:

  • TITLE with AS:迁移为AS alias。
  • TITLE with NAMED:迁移为NAMED alias。
  • TITLE with NAMED and AS:迁移为AS alias。

输入:TABLE TITLE,使用NAMED和AS

1
2
3
4
SELECT  Acct_ID (TITLE 'Acc Code') (NAMED XYZ)  AS "Account Code"
        ,Acct_Name (TITLE 'Acc Name')
FROM    GT_JCB_01030_Acct_PBU
where "Account Code" > 500  group by "Account Code" ,Acct_Name ;

输出

SELECT
          Acct_ID AS "Account Code"
          ,Acct_Name AS "Acc Name"
     FROM
          GT_JCB_01030_Acct_PBU
     WHERE
          Acct_ID > 500
     GROUP BY
          Acct_ID ,Acct_Name
;

目前,DSC支持迁移初始CREATE/ALTER语句中的TITLE命令,但不支持后续对TITLE指定列的引用。例如,在下面的CREATE TABLE语句中,带有TITLE Employee ID的列eid在迁移后被注释掉,但是SELECT语句中对eid的引用将保持原样。

输入

1
2
CREATE TABLE tab1 ( eid INT TITLE 'Employee ID');
SELECT eid FROM tab1;

输出

1
2
CREATE TABLE tab1 (eid INT /*TITLE 'Employee ID'*/);
SELECT eid from tab1;

TITLE和CREATE VIEW

输入

REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC}  
AS  
LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR ACCESS
SELECT   AUM_DATE (TITLE '    ')
      ,CLNTCODE (TITLE '    ')
      ,ACCTYPE (TITLE '    ')
      ,CCY (TITLE '  ')
      ,BAL_AMT (TITLE '  ')
      ,MON_BAL_AMT (TITLE '    ')
      ,HK_CLNTCODE (TITLE '   ')
      ,MNT_DATE (TITLE '    ')
FROM ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC};
it should be migrated as below:
CREATE OR REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} 
AS 
/*LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR ACCESS */
SELECT   AUM_DATE  /* (TITLE '    ') */
      ,CLNTCODE  /* (TITLE '    ') */
      ,ACCTYPE  /* (TITLE '    ') */
      ,CCY  /* (TITLE '  ') */
      ,BAL_AMT  /* (TITLE '  ') */
      ,MON_BAL_AMT  /* (TITLE '    ') */
      ,HK_CLNTCODE  /* (TITLE '   ') */
      ,MNT_DATE  /* (TITLE '    ') */
FROM ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC};

输出

CREATE OR REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} 
AS 
/*LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR ACCESS */
SELECT   AUM_DATE  /* (TITLE '    ') */
      ,CLNTCODE  /* (TITLE '    ') */
      ,ACCTYPE  /* (TITLE '    ') */
      ,CCY  /* (TITLE '  ') */
      ,BAL_AMT  /* (TITLE '  ') */
      ,MON_BAL_AMT  /* (TITLE '    ') */
      ,HK_CLNTCODE  /* (TITLE '   ') */
      ,MNT_DATE  /* (TITLE '    ') */
FROM ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC};