华为云用户手册

TIMESTAMP FORMAT 输入： select a.Org_Id as Brn_Org_Id /* */ ,a.Evt_Id as Vst_Srl_Nbr /* */ ,a.EAC_Id as EAC_Id /* */ ,cast(cast(cast(Prt_Tm as timestamp format 'YYYY-MM-DDBHH:MI:SS' ) as varchar(19) )as timestamp(0)) as Tsk_Start_Tm /* */ from ${BRTL_VCOR}.BRTL_BC_SLF_TMN_RTL_PRT_JNL as a /* BC_ */ where a.DW_Dat_Dt = CAST('${v_Trx_Dt}' AS DATE FORMAT 'YYYY-MM-DD') ;

TIMESTAMP(n) FORMAT 输入： select cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Snsh_Dt /* */ ,coalesce(a.CRE_DAT,cast('0001-01-01 00:00:01' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)')) as Crt_Tm /* */ ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_ETL_Dt /* */ ,cast(current_date as date format 'yyyy-mm-dd') as DW_Upd_Dt /* */ ,current_time(0) as DW_Upd_Tm /* */ ,1 as DW_Job_Seq /* */ from ${NDS_VIEW}.NLV65_MGM_GLDCUS_INF_NEW as a /* MGM */ ; ----------- cast('0001-01-01 00:00:00' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)') TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.US' ) ---------- cast('0001-01-01 00:00:00.000000' as timestamp(6)) cast('0001-01-01 00:00:00.000000' as timestamp(6)) ---------- CAST('0001-01-01 00:00:00.000000' AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)') TO_TIMESTAMP('0001-01-01 00:00:00.000000', 'yyyy-mm-dd HH24:MI:SS.US' ) ---------- cast(LA02_USERLOG_M.LOGTIME as TIMESTAMP(6) FORMAT 'YYYY-MM-DD HH:MI:SS.S(0)' ) TO_TIMESTAMP(LA02_USERLOG_M.LOGTIME, 'YYYY-MM-DD HH24:MI:SS' ) ---------- cast('0001-01-01 00:00:00' as timestamp(3) format 'yyyy-mm-ddbhh:mi:ssds(3)') TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.MS' ) ----------- CAST( '0001-01-01 00:00:01.000000' AS TIMESTAMP ( 6 ) format 'yyyy-mm-ddbhh:mi:ssds(6)' ) TO_TIMESTAMP('0001-01-01 00:00:01.000000', 'yyyy-mm-dd HH24:MI:SS.US' )

编辑临时表 用户可在Data Studio中编辑临时表。如果用户在建表时创建了连接，则断开该连接时，临时表会被自动删除。 在SQL终端编辑临时表时，请确保启用了连接重用功能。有关如何启用该功能，请参阅管理SQL终端连接。 执行以下步骤编辑临时表： 在临时表上执行查询。 “结果”页签会显示SQL查询的结果以及执行的查询语句。 “结果”页签中编辑临时表。可单击“添加”、“删除”、“保存修改”、“取消修改”编辑临时表。 父主题： 表管理

错误码 表2 错误码 错误码 错误信息 Teradata DSC_ERR_003_001 Query/statement is not supported since the Teradata view "dbc.indices" is supported only for the indextype P and Q. DSC_ERR_003_002 Error in Bteq processing. Something went wrong while processing the BTEQ commands. DSC_ERR_003_003 Query/statement is not supported in ddl DSC. Please check the same and refer user manual for the supported feature list. DSC_ERR_003_004 Unsupported format decimal format like ZZZ99Z, ZZZ.ZZ9. DSC_ERR_003_005 The tool does not support the "IN/NOT IN to EXISTS/NOT EXISTS conversion" for the query in which its outer query refers multiple tables and the column(s) specified with IN / NOT IN operator do not have table reference. DSC_ERR_003_006 The tool does not support the query in which its outer query refers multiple tables and the column(s) specified with IN / NOT IN operator do not have table reference. DSC_ERR_003_007 Primary Index without column is not supported. DSC_ERR_003_008 TeradataQuerySplitter config file contains list is not supported. DSC_ERR_003_009 Gauss does not support WITH CHECK OPTION in CREATE VIEW. Please enable the config_param tdMigrateVIEWCHECKOPTION to comment the WITH CHECK OPTION syntax in the statement. DSC_ERR_003_010 Gauss does not have an equivalent syntax for CHARACTER SET & CASE SPECIFIC option in column-level. Please enable the config_param tdMigrateCharsetCase to comment the CHARACTER SET & CASE SPECIFIC option syntax in the statement. DSC_ERR_003_011 Gauss does not have equivalent syntax for LOCK option in CREATE VIEW and INSERT statement. You can rewrite this statement or set the configuration parameter tdMigrateLOCKOption to TRUE to comment the LOCK syntax in this statement. DSC_ERR_003_012 Invalid width (Number of rows) parameter in MDIFF function. DSC_ERR_003_013 First 2 parameters should be present in MDIFF function. DSC_ERR_003_014 Query/statement is not supported as ORDER BY clause is not present in TOP WITH TIES. Please check the same and refer user manual. DSC_ERR_003_015 Column mismatch for the TITLE conversion. DSC_ERR_003_016 Query/statement is not supported as same Table alias is addressed in both inner and outer query. Please check the same and refer user manual for the supported feature list. DSC_ERR_003_017 Sub query list does not have columns. DSC_ERR_003_018 Number of expressions specified in the outer query does not match with inner query. DSC_ERR_003_019 Error while loading the .RUN FILE from given location. DSC_ERR_003_020 Unable to delete the file, file not found. DSC_ERR_003_021 Unable to delete the file, failed with IOEXception. DSC_ERR_003_022 Please specify the value for enviroment_file_path parameter in features-teradata.properties. Application DSC_ERR_004_001 Application has timed out, exceeded the hours specified in the config file. Please configure the Timeout parameter in the application.properties to higher value. DSC_ERR_004_002 Error while loading the property files from config directory. DSC_ERR_004_003 Error while loading the property files from config directory, directory is not readable. DSC_ERR_004_004 Error while loading the property file. DSC_ERR_004_005 Unable to load the JSON file. DSC_ERR_004_006 DSC tool does not support this Conversion type provided. DSC_ERR_004_007 Error occurred while framing output replacement query. DSC_ERR_004_008 Invalid index value while parsing the script. DSC_ERR_004_009 Error in conversion process, unable to convert the script. DSC_ERR_004_010 No SQL files found in the input directory with the extension specified in the fileExtension property in application.properties. DSC_ERR_004_011 The query length parameter (MaxSqlLen) value is not valid. DSC_ERR_004_012 Since the input folder has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_013 Since the output directory has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_014 Disk is almost full. Please clear the space and re-run the tool. DSC_ERR_004_015 DSC has been cancelled as configured by the user. DSC_ERR_004_016 Error occurred while formatting the sql scripts. DSC_ERR_004_017 Invalid index specified for fetching the element from list while formatting the scripts DSC_ERR_004_018 Error occurred while converting from string to integer. DSC_ERR_004_019 Input File is modified while DSC is in progress. DSC_ERR_004_020 Process is null, unable to read encoding format. DSC_ERR_004_021 Target File does not have write permissions. DSC_ERR_004_022 The target directory does not have write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_023 PL/SQL object contains incorrect DDL/Query. Please check the script for the query position specified in the log. DSC_ERR_004_024 PreQueryValidation failed due to bracket mismatch or invalid terminator. DSC_ERR_004_025 Conversion task name is not valid. DSC_ERR_004_026 Database entered by the user is not supported by the DSC tool. DSC_ERR_004_027 Gauss db password should not be empty. DSC_ERR_004_028 Gauss db password should not be empty. DSC_ERR_004_029 Target db entered in the Gaussdb.properties is not valid. DSC_ERR_004_030 User name entered in the Gaussdb.properties is empty. DSC_ERR_004_031 Port entered in the Gaussdb.properties is not valid. DSC_ERR_004_032 IP entered in the Gaussdb.properties is not valid. DSC_ERR_004_033 Database name entered in the Gaussdb.properties is empty. DSC_ERR_004_034 DSC Application failed to start. DSC_ERR_004_035 Since the Enviroment variable path has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_036 Error while loading Enviroment parameter File. DSC_ERR_004_037 Invalid input (empty/space/string value) for the parameter NoOfThreads in application.properties. Hence taking the default processes. DSC_ERR_004_038 Input for the parameter NoOfThreads in application.properties is less than 1. Hence taking the default processes. DSC_ERR_004_039 Error in processing the DDL query. DSC_ERR_004_040 Error in processing the PL/SQL query. DSC_ERR_004_041 Error in post processing the query. DSC_ERR_004_042 Invalid application timeout value, default to 4 hours. DSC_ERR_004_043 Error in writing the output file. DSC_ERR_004_044 Error in reading the input file. DSC_ERR_004_045 No valid files found in the input directory for migration. DSC_ERR_004_046 Query is not converted as it contains unsupported keyword. DSC_ERR_004_047 Error while reading the property. DSC_ERR_004_048 PreQueryValidation failed due to query exceeds maximum length (MaxSqlLen config parameter). DSC_ERR_004_049 Thread count entered in the Gaussdb.properties is not valid. Wrapper DSC_ERR_005_003 Reading file Failed with error: File not found Exception. DSC_ERR_005_004 Reading file Failed with error: IOException. DSC_ERR_005_005 Root privileged users are not allowed to execute the DSC tool. DSC_ERR_005_006 Error while getting the id of os user used to execute the DSC tool. DSC_ERR_005_007 Arguments specified is not valid, please check the user manual for the command line arguments. DSC_ERR_005_008 File name is not specified for reading the encoding type. DSC_ERR_005_009 Invalid argument specified for the encoding parameter. DSC_ERR_005_010 Source database is not set. Please enter a valid source db and refer the user manual for syntax. DSC_ERR_005_011 Commandline database specified for source to target is not supported by the DSC tool. DSC_ERR_005_012 Error in loading config file with IOException. DSC_ERR_005_013 Initial JVM memory is greater than maximum JVM memory. DSC_ERR_005_014 Invalid value specified for configValue. DSC_ERR_005_015 Invalid source database specified for source-db option. DSC_ERR_005_016 Invalid target database specified for target-db option. DSC_ERR_005_017 Invalid conversion type specified for dsc-type option. DSC_ERR_005_018 Invalid application language specified for application-lang option. DSC_ERR_005_019 Conversion-type should be DDL for application-lang type as perl. DSC_ERR_005_020 Source-db should be teradata for application-lang type as perl. DSC_ERR_005_021 Please use "-VN [V1R7 | V1R8_330]" or "--version-number [V1R7 | V1R8_330]" to specify the kernel version which can be either V1R7 or V1R8_330. DSC_ERR_005_022 Input directory does not exist. DSC_ERR_005_023 Getting path for input directory failed with IOException. DSC_ERR_005_024 Getting path for output directory failed with IOException. DSC_ERR_005_025 Setting file permission for output directory failed with IOException. DSC_ERR_005_026 Creating output directory failed. DSC_ERR_005_027 Setting file permissions for log directory/file failed with FileException. DSC_ERR_005_028 Error while connecting to GaussDB, Failed with error. DSC_ERR_005_029 Error occurred due to file permission while creating or executing the file. DSC_ERR_005_030 No arguments specified in the commandline. DSC_ERR_005_031 Error occurred in creating output directory.

MULTISET MULTISET是一个普通表，所有数据库都支持这个表。迁移工具同时支持MULTISET和SET表。 MULTISET表支持与VOLATILE一起使用。 输入：CREATE MULTISET TABLE 1 CREATE VOLATILE MULTISET TABLE T1 (c1 int ,c2 int); 输出： 1 2 3 4 5 6 7 CREATE LOCAL TEMPORARY TABLE T1 ( c1 INTEGER ,c2 INTEGER ) ; 父主题： 表迁移

CREATE TABLE Teradata的CREATE TABLE （缩写关键字为CT）语句用于创建表。 示例： 输入：CREATE TABLE 1 2 3 CT tab1 ( id INT ); 输出： 1 2 3 4 5 6 CREATE TABLE tab1 ( id INTEGER ) ; 执行CREATE tab2 AS tab1时，从tab1中复制的结构将用于创建表tab2。如果CREATE TABLE语句包含WITH DATA选项，则会将tab1的数据也复制到tab2中。使用CREATE AS时，源表中的CONSTRAINT行将保留在新表中。 如果•session_mode设为Teradata，则必须删除目标表中的重复记录。该操作通过在迁移脚本中添加MINUS运算符实现。 如果•session_mode设为ANSI，则允许目标表中存在重复记录。 如果源表具有PRIMARY KEY（主键）或UNIQUE CONSTRAINT（唯一约束），则该表不包含任何重复记录。在这种情况下，不需要添加MINUS操作符删除重复的记录。 示例： 输入：CREATE TABLE AS WITH DATA（session_mode=Teradata） 1 2 CREATE TABLE tab2 AS tab1 WITH DATA; 输出： 1 2 3 4 5 6 7 8 9 10 11 BEGIN CREATE TABLE tab2 ( LIKE tab1 INCLUDING ALL EXCLUDING PARTITION EXCLUDING RELOPTIONS ); INSERT INTO tab2 SELECT * FROM tab1 MINUS SELECT * FROM tab2; END ; / 输入：CREATE TABLE AS WITH DATA AND STATISTICS 1 2 3 4 CREATE SET VOLATILE TABLE tab2025 AS ( SELECT * from tab2023 ) WITH DATA AND STATISTICS PRIMARY INDEX (LOGTYPE, OPERSEQ); 输出： 1 2 3 4 5 CREATE LOCAL TEMPORARY TABLE tab2025 DISTRIBUTE BY HASH ( LOGTYPE, OPERSEQ ) AS ( SELECT * FROM tab2023 ); ANALYZE tab2025; 父主题： 表迁移

COLUMN STORE 表的存储方式可使用CREATE TABLE语句中的WITH（ORIENTATION=COLUMN）从ROW-STORE转换为COLUMN存储。可使用rowstoreToColumnstore参数启用/禁用此功能。 输入：CREATE TABLE，修改存储模式为 COLUMN STORE 1 2 3 4 5 6 7 CREATE MULTISET VOLATILE TABLE tab1 ( c1 VARCHAR(30) CHARACTER SET UNICODE , c2 DATE , ... ) PRIMARY INDEX (c1, c2) ON COMMIT PRESERVE ROWS; 输出： 1 2 3 4 5 6 7 CREATE LOCAL TEMPORARY TABLE tab1 ( c1 VARCHAR(30) , c2 DATE , ... ) WITH (ORIENTATION = COLUMN) ON COMMIT PRESERVE ROWS DISTRIBUTE BY HASH (c1, c2); 父主题： 表迁移

TRANSACTION DSC工具在迁移MySQL事务处理语句时会根据GaussDB(DWS)特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ##该声明仅适用于会话中执行的下一个单个事务 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ ONLY; SET TRANSACTION READ WRITE; SET TRANSACTION ISOLATION LEVEL READ COMMITTED,READ ONLY; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,READ WRITE; ##使用SESSION关键字,适用于当前会话中执行的所有后续事务 START TRANSACTION; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; commit ; 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 --该声明仅适用于会话中执行的下一个单个事务 SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET LOCAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET LOCAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET LOCAL TRANSACTION READ ONLY; SET LOCAL TRANSACTION READ WRITE; SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY; SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE; --使用SESSION关键字,适用于当前会话中执行的所有后续事务 START TRANSACTION; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; COMMIT WORK; 父主题： 事务管理

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};

CHECK OPTION 通过tdMigrateVIEWCHECKOPTIO....参数可以配置如何迁移包含CHECK OPTION关键字的视图。 如果源数据库中出现含有CHECK OPRTION关键词的视图，则工具在目标数据库中注释掉CHECK OPRTION。 输入：VIEW，使用CHECK OPTION 1 2 3 4 5 6 CV mgr15 AS SEL * FROM employee WHERE manager_id = 15 WITH CHECK OPTION ; 输出：（tdMigrateVIEWCHECKOPTION=True） 1 2 3 4 5 6 7 8 9 10 CREATE OR REPLACE VIEW mgr15 AS ( SELECT * FROM employee WHERE manager_id = 15 /*WITH CHECK OPTION */ ) ; 输出：（tdMigrateVIEWCHECKOPTION=False） 1 2 3 4 5 6 CV mgr15 AS SEL * FROM employee WHERE manager_id = 15 WITH CHECK OPTION ;

REPLACE FUNCTION 输入： REPLACE FUNCTION up_load1.RPT_016_BUS_DATE() RETURNS DATE LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN DATE'2017-08-22'; 输出： CREATE OR REPLACE FUNCTION up_load1.RPT_016_BUS_DATE() RETURNS DATE LANGUAGE SQL IMMUTABLE SECURITY DEFINER AS $$ SELECT CAST('2017-08-20' AS DATE) $$ ;

VIEW WITH ACCESS LOCK 通过tdMigrateLOCKOption参数可以配置如何迁移包含LOCK关键字的查询。如果tdMigrateLOCKOption设置为false，则该工具在迁移时将跳过该查询并记录日志。 输入：VIEW，使用ACCESS LOCK 1 2 3 4 5 6 7 8 CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN AS LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS FOR ACCESS ( SELECT RUN_ID, PRODUCT_ID, CURRENCY , CASHFLOW, ENTITY, LCR , TIME_BUCKET, MT, Ctl_Id , File_Id, Business_Date FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ; 输出： 1 2 3 4 5 6 7 8 CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN AS /* LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS */ ( SELECT RUN_ID, PRODUCT_ID, CURRENCY , CASHFLOW, ENTITY, LCR , TIME_BUCKET, MT, Ctl_Id , File_Id, Business_Date FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ; dbc.columnsV 输入： SELECT A.ColumnName AS V_COLS ,A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV') THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE '' END||'CHAR('||TRIM(columnlength (INT))|| ') CHARACTER SET LATIN'|| CASE WHEN UpperCaseFlag='N' THEN ' NOT' ELSE '' END || ' CASESPECIFIC' WHEN columnType='DA' THEN 'DATE' WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')' WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')' WHEN columnType='I' THEN 'INTEGER' WHEN columnType='I1' THEN 'BYTEINT' WHEN columnType='I2' THEN 'SMALLINT' WHEN columnType='I8' THEN 'BIGINT' WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')' ELSE 'Unknown' END||CASE WHEN Nullable='Y' THEN '' ELSE ' NOT NULL' END||'0A'XC AS V_ColT ,D.ColumnName AS V_PICol --获得目标表主索引 FROM dbc.columnsV A LEFT JOIN dbc.IndicesV B ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = '${V_TDDLDB}' AND B.tablename='${TARGET_TABLE}' WHERE A.databasename='${V_TDDLDB}' AND A.tablename = '${TARGET_TABLE}' AND A.columnname NOT IN ( 'ETL_JOB_NAME' ,'ETL_TX_DATE' , 'ETL_PROC_DATE' )ORDER BY A.columnid; 输出： D DECLARE lv_mig_V_COLS TEXT; lv_mig_V_ColT TEXT; lv_mig_V_PICol TEXT; BEGIN SELECT STRING_AGG(A.ColumnName, ',') , STRING_AGG(A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV') THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE '' END||'CHAR('||TRIM(mig_td_ext.mig_fn_castasint(columnlength))|| ') /*CHARACTER SET LATIN*/'|| CASE WHEN UpperCaseFlag='N' THEN ' NOT' ELSE '' END || ' /*CASESPECIFIC*/' WHEN columnType='DA' THEN 'DATE' WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')' WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')' WHEN columnType='I' THEN 'INTEGER' WHEN columnType='I1' THEN 'BYTEINT' WHEN columnType='I2' THEN 'SMALLINT' WHEN columnType='I8' THEN 'BIGINT' WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')' ELSE 'Unknown' END||CASE WHEN Nullable='Y' THEN '' ELSE ' NOT NULL' END||E'\x0A', ',') , STRING_AGG(B.ColumnName, ',') INTO lv_mig_V_COLS, lv_mig_V_ColT, lv_mig_V_PICol FROM mig_td_ext.vw_td_dbc_columnsV A LEFT JOIN mig_td_ext.vw_td_dbc_IndicesV B ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = 'public' AND B.tablename='emp2' WHERE A.databasename='public' AND A.tablename = 'emp2'; -- ORDER BY A.columnid; END; /

REPLACE VIEW 在Teradata中，REPLACE VIEW语句用于创建新视图，或重建现有视图。DSC将其迁移为GaussDB T、GaussDB A 和 DWS中兼容的CREATE OR REPLACE VIEW语句中。 输入：REPLACE VIEW 1 2 3 4 5 6 7 REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT RUNDATE FROM DP_STEDW.DATE_TBL WHERE dummy = 1 ; 输出： 1 2 3 4 5 6 7 8 9 10 CREATE OR REPLACE VIEW DP_STEDW.MY_PARAM AS ( SELECT RUNDATE FROM DP_STEDW.DATE_TBL WHERE dummy = 1 ) ; 输入：REPLACE RECURSIVE VIEW 1 2 3 4 5 6 Replace RECURSIVE VIEW reachable_from ( emp_id,emp_name,DEPTH) AS ( SELECT root.emp_id,root.emp_name,0 AS DEPTH FROM emp AS root WHERE root.mgr_id IS NULL); 输出： 1 2 3 4 5 6 7 8 CREATE OR REPLACE VIEW reachable_from AS ( WITH RECURSIVE reachable_from ( emp_id,emp_name,DEPTH) AS ( SELECT root.emp_id,root.emp_name,0 AS DEPTH FROM emp AS root WHERE root.mgr_id IS NULL ) SELECT * FROM reachable_from);

LOCK DSC工具在迁移MySQL 事务处理锁表语句时会根据GaussDB(DWS)特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 ## A. START TRANSACTION; LOCK TABLES `mt`.`runoob_tbl` WRITE,`mt`.`runoob_tb2` READ; commit; ## B. START TRANSACTION; LOCK TABLES `mt`.`runoob_tbl` WRITE; commit; ## C. START TRANSACTION; LOCK TABLES `mt`.`runoob_tbl` READ,`mt`.`runoob_tbl` AS t1 READ; commit; 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -- A. START TRANSACTION; LOCK TABLE "mt"."runoob_tbl" IN ACCESS EXCLUSIVE MODE; LOCK TABLE "mt"."runoob_tb2" IN ACCESS SHARE MODE; COMMIT WORK; -- B. START TRANSACTION; LOCK TABLE "mt"."runoob_tbl" IN ACCESS EXCLUSIVE MODE; COMMIT WORK; -- C. START TRANSACTION; LOCK TABLE "mt"."runoob_tbl" IN ACCESS SHARE MODE; COMMIT WORK; 父主题： 事务管理

FORMAT和CAST Teradata中，关键词FORMAT用于格式化列或表达式。例如，LPAD中FORMAT '9(n)'和'z(n)'分别用'0'和空格(' ')表示。 数据类型转换可使用CAST或直接数据类型（[like (expression1)(CHAR(n))]）进行。该功能使用CAST实现。详情参见类型转换和格式化。 输入：FORMAT和CAST 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT CAST(TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) FORMAT '9(5)' ) FROM C03_AGENT_BOND ; SELECT CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) ) FROM C03_AGENT_BOND ; SELECT CHAR(CAST( CAST( CND_VLU AS DECIMAL( 17 ,0 ) FORMAT 'Z(17)' ) AS VARCHAR( 17 ) ) ) FROM C03_AGENT_BOND ; 输出： SELECT LPAD( CAST( TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) ) ,5 ,'0' ) AS Agt_Num FROM C03_AGENT_BOND ; SELECT CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) ) FROM C03_AGENT_BOND ; SELECT LENGTH( CAST( LPAD( CAST( CND_VLU AS DECIMAL( 17 ,0 ) ) ,17 ,' ' ) AS VARCHAR( 17 ) ) ) AS CND_VLU FROM C03_AGENT_BOND ; 输入：FORMAT 'Z(n)9' 1 2 3 4 5 6 SELECT standard_price (FORMAT 'Z(5)9') (CHAR( 6 )) ,max_price (FORMAT 'ZZZZZ9') (CHAR( 6 )) FROM product_t ; 输出： 1 2 3 4 5 6 SELECT CAST( TO_CHAR( standard_price ,'999990' ) AS CHAR( 6 ) ) AS standard_price ,CAST( TO_CHAR( max_price ,'999990' ) AS CHAR( 6 ) ) AS max_price FROM product_t ; 输入：FORMAT 'z(m)9.9(n)' 1 2 3 4 5 SELECT standard_price (FORMAT 'Z(6)9.9(2)') (CHAR( 6 )) FROM product_t ; 输出： 1 2 3 4 5 SELECT CAST( TO_CHAR( standard_price ,'9999990.00' ) AS CHAR( 6 ) ) AS standard_price FROM product_t ; 输入：CAST AS INTEGER 1 2 3 4 5 SELECT CAST( standard_price AS INTEGER ) FROM product_t ; 输出： 1 2 3 4 5 SELECT (standard_price) FROM product_t ; 输入：CAST AS INTEGER FORMAT 1 2 3 4 5 6 7 SELECT CAST( price11 AS INTEGER FORMAT 'Z(4)9' ) ( CHAR( 10 ) ) FROM product_t ; 输出： 1 2 3 4 5 SELECT CAST( TO_CHAR( ( price11 ) ,'99990' ) AS CHAR( 10 ) ) AS price11 FROM product_t ; 新增以下Gauss函数来转换为INTEGER： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE OR REPLACE FUNCTION /* This function is used to support "CAST AS INTEGER" of Teradata. It should be created in the "mig_td_ext" schema. */ ( i_param TEXT ) RETURN INTEGER AS v_castasint INTEGER; BEGIN v_castasint := CASE WHEN i_param IS NULL THEN NULL -- if NULL value is provided as input WHEN TRIM(i_param) IS NULL THEN 0 -- if empty string with one or more spaces is provided ELSE TRUNC(CAST(i_param AS NUMBER)) -- if any numeric value is provided END; RETURN v_castasint; END; 父主题： 查询迁移操作符

创建带索引分区表 如果配置参数tdMigrateRANGE_N为true。 输入： CREATE SET TABLE SC.TAB , NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM=DEFAULT, DEFAULT MERGEBLOCKRATIO ( ACCOUNT_NUM VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL ,ACCOUNT_MODIFIER_NUM CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL ,END_DT DATE FORMAT 'YYYY-MM-DD' ,UPD_TXF_BATCHTD INTEGER COMPRESS ) PRIMARY INDEX XPKT0300_AGREEMENT (ACCOUNT_NUM,ACCOUNT_MODIFIER_NUM) PARTITION BY RANGE_N(END_DT BETWEEN '2001-01-01' AND '2020-12-31' EACH INTERVAL '1' DAY, NO RANGE ,UNKNOWN) INDEX (UPD_TXF_BATCHTD) ; 输出： CREATE TABLE SC.TAB ( ACCOUNT_NUM VARCHAR( 255 ) /* CHARACTER SET LATIN*/ /* NOT CASESPECIFIC*/ NOT NULL ,ACCOUNT_MODIFIER_NUM CHAR( 18 ) /* CHARACTER SET LATIN*/ /* NOT CASESPECIFIC*/ NOT NULL ,END_DT DATE ,UPD_TXF_BATCHTD INTEGER /* COMPRESS */ ) DISTRIBUTE BY HASH ( ACCOUNT_NUM ,ACCOUNT_MODIFIER_NUM ) PARTITION BY RANGE (END_DT) ( PARTITION TAB_1 start ('2001-01-01') END ('2020-12-31') EVERY ( INTERVAL '1' DAY ) ) ; CREATE INDEX ON SC.TAB (UPD_TXF_BATCHTD) LOCAL;

CHARACTER SET和CASESPECIFIC CHARACTER SET用于指定字符列的服务器字符集，CASESPECIFIC用于指定字符数据比较及排序时的大小写情况。 可以使用tdMigrateCharsetCase参数来配置是否迁移CHARACTER SET和CASESPECIFIC。如果该参数设为false，则工具将跳过该查询的迁移并记录消息。 输入：tdMigrateCharsetCase=True 1 2 3 4 5 6 7 8 CREATE MULTISET VOLATILE TABLE TAB1 ( col1 INTEGER NOT NULL ,col2 INTEGER NOT NULL ,col3 VARCHAR(100) NOT NULL CHARACTER SET UNICODE CASESPECIFIC ) PRIMARY INDEX (col1,col2) ON COMMIT PRESERVE ROWS ; 输出： 1 2 3 4 5 6 7 8 9 10 CREATE LOCAL TEMPORARY TABLE TMP_RATING_SYS_PARA ( col1 INTEGER NOT NULL ,col2 INTEGER NOT NULL ,col3 VARCHAR(100) NOT NULL /* CHARACTER SET UNICODE CASESPECIFIC */) ) ON COMMIT PRESERVE ROWS DISTRIBUTE BY HASH (col1,col2) ; 输入：迁移支持的字符数据类型 在Teradata中，以下字符集支持以字符个数来衡量字符串数据类型的长度： LATIN UNICODE GRAPHIC 不过，KANJISJIS字符集支持以字节个数来衡量字符串数据类型的长度。 以COLUMN_NAME VARCHAR(100) CHARACTER SET UNICODE CASESPECIFIC COLUMN_NAME VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC为例，字符串最大支持100个字符（而不是字节）。 在GaussDB A中，字符串数据类型长度通过字节（而不是字符）来衡量。 VARCHAR(100)和VARCHAR2(100)最多支持100个字节（而不是字符）。 但是NVARCHAR2(100)最大可支持100个字符。 因此，如果Teradata使用LATIN、UNICODE或GRAPHIC字符集，VARCHAR应迁移为NVARCHAR。 1 2 3 4 5 CREATE TABLE tab1 ( col1 VARCHAR(10), COL2 CHAR(1) ); 输出： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 a)when default_charset = UNICODE/GRAPHIC CREATE TABLE tab1 ( col1 NVARCHAR2 (10) ,COL2 NVARCHAR2 (1) ) ; b)when default_charset = LATIN CREATE TABLE tab1 ( col1 VARCHAR2 (10) ,COL2 VARCHAR2 (1) ) ; 输入： 1 2 3 4 5 CREATE TABLE tab1 ( col1 VARCHAR(10) CHARACTER SET UNICODE, COL2 CHAR(1) ); 输出： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 a) when default_charset = UNICODE/GRAPHIC CREATE TABLE tab1 ( col1 NVARCHAR2 (10) /* CHARACTER SET UNICODE*/ ,COL2 NVARCHAR2( 1 ) ) ; b) when default_charset = LATIN CREATE TABLE tab1 ( col1 NVARCHAR2 (10) /* CHARACTER SET UNICODE*/ ,COL2 CHAR(1) ) ; 父主题： 表迁移

RANGE_N with * for start-date 输入： CREATE MULTISET TABLE Orders5 ( StoreNo SMALLINT, OrderNo INTEGER, OrderDate DATE, OrderTotal INTEGER ) PRIMARY INDEX(OrderNo) PARTITION BY RANGE_N ( OrderDate BETWEEN DATE * AND DATE '2016-12-31' EACH INTERVAL '1' YEAR, DATE '2017-01-01' EACH INTERVAL '1' MONTH, DATE '2020-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY ); 输出： CREATE TABLE Orders5 ( StoreNo SMALLINT, OrderNo INTEGER, OrderDate DATE, OrderTotal INTEGER ) DISTRIBUTE BY HASH (OrderNo) PARTITION BY RANGE (OrderDate) ( PARTITION Orders5_0 START (DATE '0001-01-01') END (DATE '2016-12-31') EVERY (INTERVAL '1' YEAR), PARTITION Orders5_1 START (DATE '2016-12-31') END (DATE '2020-01-01') EVERY (INTERVAL '1' MONTH), PARTITION Orders5_2 START (DATE '2020-01-01') END (DATE '2020-12-31') EVERY (INTERVAL '1' DAY) );

RANGE_N with * for end-date 输入： CREATE SET TABLE Orders4 ( StoreNo SMALLINT, OrderNo INTEGER, OrderDate DATE, OrderTotal INTEGER ) PRIMARY INDEX(OrderNo) PARTITION BY RANGE_N ( OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' YEAR, DATE '2017-01-01' EACH INTERVAL '1' MONTH, DATE '2019-01-01' AND * ); 输出： CREATE TABLE Orders4 ( StoreNo SMALLINT, OrderNo INTEGER, OrderDate DATE, OrderTotal INTEGER ) DISTRIBUTE BY HASH (OrderNo) PARTITION BY RANGE (OrderDate) ( PARTITION Orders4_0 START (DATE '2010-01-01') END (DATE '2016-12-31') EVERY (INTERVAL '1' YEAR), PARTITION Orders4_1 START (DATE '2016-12-31') END (DATE '2020-01-01') EVERY (INTERVAL '1' MONTH) , PARTITION Orders4_2 START (DATE '2020-01-01') END (MAXVALUE) );

RANGE_N在字符串列的分区 输入： CREATE SET TABLE SC.TAB , NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM=DEFAULT, DEFAULT MERGEBLOCKRATIO ( ACCOUNT_NUM VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL ,ACCOUNT_MODIFIER_NUM CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL ,DATA_SOURCE_ID CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC ,END_DT DATE FORMAT 'YYYY-MM-DD' ,UPD_TXF_BATCHTD INTEGER COMPRESS ) PRIMARY INDEX XPKT0300_AGREEMENT (ACCOUNT_NUM,ACCOUNT_MODIFIER_NUM) PARTITION BY ( RANGE_N(DATA_SOURCE_ID BETWEEN 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' AND 'ZZ', NO RANGE ,UNKNOWN) ,CASE_N(END_DT IS NULL , NO CASE , UNKNOWN)) ; 输出： CREATE TABLE SC.TAB ( ACCOUNT_NUM VARCHAR( 255 ) /* CHARACTER SET LATIN*/ /* NOT CASESPECIFIC*/ NOT NULL ,ACCOUNT_MODIFIER_NUM CHAR( 18 ) /* CHARACTER SET LATIN*/ /* NOT CASESPECIFIC*/ NOT NULL ,DATA_SOURCE_ID CHAR( 10 ) /* CHARACTER SET LATIN*/ /* NOT CASESPECIFIC*/ ,END_DT DATE ,UPD_TXF_BATCHTD INTEGER /* COMPRESS */ ) DISTRIBUTE BY HASH ( ACCOUNT_NUM ,ACCOUNT_MODIFIER_NUM )/* PARTITION BY ( RANGE_N ( DATA_SOURCE_ID BETWEEN 'A' ,'B' ,'C' ,'D' ,'E' ,'F' ,'G' ,'H' ,'I' ,'J' ,'K' ,'L' ,'M' ,'N' ,'O' ,'P' ,'Q' ,'R' ,'S' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Y' ,'Z' AND 'ZZ' ,NO RANGE ,UNKNOWN ) ,*/ /* CASE_N(END_DT IS NULL , NO CASE , UNKNOWN)) */ ;

RANGE_N with comma seperated values 输入： CREATE TABLE orders10 (storeid INTEGER NOT NULL ,productid INTEGER NOT NULL ,orderdate DATE NOT NULL ,totalorders INTEGER NOT NULL) PRIMARY INDEX (storeid, productid) PARTITION BY ( RANGE_N(totalorders BETWEEN *, 100, 1000 AND *) ); 输出： CREATE TABLE orders10 (storeid INTEGER NOT NULL ,productid INTEGER NOT NULL ,orderdate DATE NOT NULL ,totalorders INTEGER NOT NULL) DISTRIBUTE BY HASH (storeid, productid) PARTITION BY RANGE (totalorders) ( PARTITION Orders10_0 END (100), PARTITION Orders10_1 END (1000), PARTITION Orders10_2 END (MAXVALUE) );

RANGE_N with different partition INTERVAL 输入： CREATE MULTISET TABLE tab1 ( TICD VARCHAR(10) , TCIT VARCHAR(10) , TCCM VARCHAR(50) , DW_Stat_Dt DATE ) PRIMARY INDEX ( TICD ) PARTITION BY RANGE_N ( DW_Stat_Dt BETWEEN DATE '0001-01-01' AND DATE '0001-01-04' EACH INTERVAL '1' DAY, DATE '0001-01-05' AND DATE '1899-12-31', DATE '1900-01-01' AND DATE '1900-01-01', DATE '1900-01-02' AND DATE '1999-12-31', DATE '2000-01-01' AND DATE '2009-12-31' EACH INTERVAL '1' YEAR, DATE '2010-01-01' AND DATE '2021-12-31' EACH INTERVAL '1' DAY, DATE '9999-12-31' AND DATE '9999-12-31', NO RANGE ); 输出： CREATE TABLE tab1 ( TICD VARCHAR( 10 ) , TCIT VARCHAR( 10 ) , TCCM VARCHAR( 50 ) , DW_Stat_Dt DATE ) DISTRIBUTE BY HASH (TICD) PARTITION BY RANGE (DW_Stat_Dt) ( PARTITION tab1_0 START (DATE '0001-01-01') END (DATE '0001-01-04') EVERY (INTERVAL '1' DAY), PARTITION tab1_1 START (DATE '0001-01-04') END (DATE '1899-12-31'), PARTITION tab1_2 START (DATE '1899-12-31') END (DATE '1900-01-01'), PARTITION tab1_ 3 START (DATE '1900-01-01') END (DATE '1999-12-31'), PARTITION tab1_4 START (DATE '1999-12-31') END (DATE '2009-12-31') EVERY (INTERVAL '1' YEAR) , PARTITION tab1_5 START (DATE '2009-12-31') END (DATE '2021-12-31') EVERY (INTERVAL '1' DAY) , PARTITION tab1_6 START (DATE '2021-12-31') END (DATE '9999-12-31') );

SET SET是Teradata的独有功能。它不允许重复的记录。它使用MINUS集合操作符来实现。DSC支持MULTISET和SET表。SET表支持与VOLATILE一起使用。 输入：SET TABLE 1 2 3 4 5 CREATE SET VOLATILE TABLE tab1 … ; INSERT INTO tab1 SELECT expr1, expr2, … FROM tab1, … WHERE ….; 输出： 1 2 3 4 5 6 7 CREATE LOCAL TEMPORARY TABLE tab1 … ; INSERT INTO tab1 SELECT expr1, expr2, … FROM tab1, … WHERE …. MINUS SELECT * FROM tab1 ; 父主题： 表迁移

索引迁移 Teradata中CREATE INDEX的列和表名的顺序和GaussDB T、GaussDB A 和GaussDB(DWS)中不同。使用参数distributeByHash配置数据在集群节点间的分布方式。该工具不会添加DISTRIBUTE BY HASH用于创建具有主键和非唯一主索引的表。 输入：主键非主索引的超集，且仅有1列匹配 1 2 3 4 5 6 CREATE TABLE good_5 ( column_1 INTEGER NOT NULL PRIMARY KEY ,column_2 INTEGER ,column_3 INTEGER NOT NULL ,column_4 INTEGER ) PRIMARY INDEX (column _1,column_2); 输出： 1 2 3 4 5 6 7 CREATE TABLE good_5 ( column_1 INTEGER NOT NULL PRIMARY KEY ,column_2 INTEGER ,column_3 INTEGER NOT NULL ,column_4 INTEGER ) ; 输入：主键非主索引的超集，且无匹配的列 1 2 3 4 5 6 7 8 9 10 11 12 CREATE SET TABLE DP_SEDW.T_170UT_HOLDER_ACCT ,NO FALLBACK ,NO BEFORE JOURNAL ,NO AFTER JOURNAL ( BUSINESSDATE VARCHAR( 10 ) ,SOURCESYSTEM VARCHAR( 5 ) ,UPLOADCODE VARCHAR( 1 ) ,HOLDER_NO VARCHAR( 7 ) NOT NULL ,POSTAL_ADD_4 VARCHAR( 40 ) ,EPF_IND CHAR( 1 ) ,PRIMARY KEY ( UPLOADCODE ,HOLDER_NO ) ) PRIMARY INDEX ( SOURCESYSTEM,EPF_IND ); 输出： 1 2 3 4 5 6 7 8 CREATE TABLE DP_SEDW.T_170UT_HOLDER_ACCT ( BUSINESSDATE VARCHAR( 10 ) ,SOURCESYSTEM VARCHAR( 5 ) ,UPLOADCODE VARCHAR( 1 ) ,HOLDER_NO VARCHAR( 7 ) NOT NULL ,POSTAL_ADD_4 VARCHAR( 40 ) ,EPF_IND CHAR( 1 ) ,PRIMARY KEY (UPLOADCODE ,HOLDER_NO ) ); 输入：不存在主键，且唯一索引有名称 1 2 3 4 5 6 7 8 9 10 11 CREATE SET TABLE "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL ( Organization_Party_Id INTEGER NOT NULL , Function_Code SMALLINT NOT NULL , Intern_Funct_Strt_Date DATE FORMAT 'YYYY-MM-DD' NOT NULL , Intern_Funct_End_Date DATE FORMAT 'YYYY-MM-DD' ) PRIMARY INDEX ( Organization_Party_Id ) UNIQUE INDEX ux_t0409_intr_fn_1 ( Function_Code, Intern_Funct_Strt_Date ) UNIQUE INDEX ( Organization_Party_Id, Intern_Funct_Strt_Date ); 输出： 1 2 3 4 5 6 7 8 9 CREATE TABLE "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Organization_Party_Id INTEGER NOT NULL , Function_Code SMALLINT NOT NULL , Intern_Funct_Strt_Date DATE NOT NULL , Intern_Funct_End_Date DATE ) DISTRIBUTE BY HASH ( Organization_Party_Id ); CREATE INDEX ux_t0409_intr_fn_1 ON "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Function_Code, Intern_Funct_Strt_Date ); CREATE UNIQUE INDEX ON "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Organization_Party_Id, Intern_Funct_Strt_Date ); 输入：CREATE TABLE，使用主键和非唯一主索引（未添加DISTRIBUTE BY HASH） 1 2 3 4 5 6 7 8 9 10 CREATE TABLE employee ( EMP_NO INTEGER , DEPT_NO INTEGER , FIRST_NAME VARCHAR(20) , LAST_NAME CHAR(20) , SALARY DECIMAL(10,2) , ADDRESS VARCHAR(100) , CONSTRAINT pk_emp PRIMARY KEY ( EMP_NO ) ) PRIMARY INDEX ( DEPT_NO ) ; 输出： 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE employee ( EMP_NO INTEGER , DEPT_NO INTEGER , FIRST_NAME VARCHAR(20) , LAST_NAME CHAR(20) , SALARY DECIMAL(10,2) , ADDRESS VARCHAR(100) , CONSTRAINT pk_emp PRIMARY KEY ( EMP_NO ) ) ; 父主题： 模式对象与数据定义（DDL）

LIKE ALL/NOT LIKE ALL 输入：LIKE ALL/NOT LIKE ALL 1 2 3 SELECT c1, c2 FROM tab1 WHERE c3 NOT LIKE ALL ('%STR1%', '%STR2%', '%STR3%'); 输出： 1 2 3 SELECT c1, c2 FROM tab1 WHERE c3 NOT LIKE ALL (ARRAY[ '%STR1%', '%STR2%', '%STR3%' ]);

LIKE ANY/NOT LIKE ANY 输入：LIKE ANY/NOT LIKE ANY 1 2 3 SELECT c1, c2 FROM tab1 WHERE c3 LIKE ANY ('STR1%', 'STR2%', 'STR3%'); 输出： 1 2 3 SELECT c1, c2 FROM tab1 WHERE c3 LIKE ANY (ARRAY[ 'STR1%', 'STR2%', 'STR3%' ]);

QUALIFY、CASE和ORDER BY 输入： select a.Cust_UID as Cust_UID /* UID */ ,a.Rtl_Usr_Id as Ini_CM /* */ ,a.Cntr_Aprv_Dt as Aprv_Pass_Tm /* */ ,a.Blg_Org_Id as CM_BRN_Nbr /* */ ,a.Mng_Chg_Typ_Cd as MNG_CHG_TYP_CD /* */ ,case when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'PMD' and a.Pst_Id in ('PB0101','PB0104') then 'Y' ---- , when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DEVPMD' and a.Pst_Id ='PB0106' then 'Y' ---- when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DMD' and a.Pst_Id in ('PB0201','PB0204') then 'Y' ---- , when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DEVDMD' and a.Pst_Id ='PB0109' then 'Y' ---- , else '' end as Pst_Flg /* */ ,a.Pst_Id as Pst_Id /* */ ,a.BBK_Org_Id as BBK_Org_Id /* */ from VT_CUID_MND_NMN_CHG_INF as a /* VT_ */ LEFT OUTER JOIN ${BRTL_VCOR}.BRTL_EM_USR_PST_REL_INF_S as b /* EM_ */ on a.Rtl_Usr_Id = b.Rtl_Usr_Id AND a.Blg_Org_Id = b.BRN_Org_Id AND a.Pst_Id = b.Pst_Id AND b.Sys_Id = 'privatebanking' AND b.pst_sts IN ('1','0','-2') /* 1 -2 0 */ AND b.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') qualify row_number() over(partition by a.Cust_UID,a.bbk_org_id order by case when ( a.Mng_Chg_Typ_Cd= 'PMD' and a.Pst_Id in ('PB0101','PB0104')) or ( a.Mng_Chg_Typ_Cd= 'DEVPMD' and a.Pst_Id ='PB0106') then 0 when (a.Mng_Chg_Typ_Cd= 'DMD' and a.Pst_Id in ('PB0201','PB0204')) or (a.Mng_Chg_Typ_Cd= 'DEVDMD' and a.Pst_Id ='PB0109 ') then 0 else 1 end asc ) = 1 ; 输出：

声明Hexadecimal Binary Literal值 输入： CREATE MULTISET TABLE bvalues (IDVal INTEGER, CodeVal BYTE(2)); INSERT INTO bvalues VALUES (112193, '7879'XB) ; SELECT IDVal, CodeVal FROM bvalues WHERE CodeVal = '7879'XB ; 输出：

声明Hexadecimal Character Literal值 输入： SELECT (COALESCE(TRIM(BOTH FROM VTX_D_RPT_0017_WMSE12_01_01.ID),'')) ||'7E'xc||(COALESCE(TRIM(BOTH FROM VTX_D_RPT_0017_WMSE12_01_01.Code),'')) ||'7E'xc||(COALESCE(TRIM(BOTH FROM VTX_D_RPT_0017_WMSE12_01_01.Description),'')) ||'7E'xc||(COALESCE(TRIM(BOTH FROM VTX_D_RPT_0017_WMSE12_01_01.Name),'')) ||'7E'xc||(COALESCE(TRIM(BOTH FROM VTX_D_RPT_0017_WMSE12_01_01.Host_Product_Id),'')) FROM DP_VTXEDW.VTX_D_RPT_0017_WMSE12_01_01 VTX_D_RPT_0017_WMSE12_01_01 WHERE 1=1 ; 输出：

Teradata 如果含有FORMAT参数的case语句未用半角括号“()”括起来，该语句不会处理。 例如： 1 case when column1='0' then column1='value' end (FORMAT 'YYYYMMDD')as alias1 在该示例中，case when column1='0' then column1='value' end未用半角括号括起，因此不会处理该语句。