更新时间：2024-04-18 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};
父主题： 表迁移
