文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Teradata语法迁移/ 类型转换和格式化
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

类型转换和格式化

本节主要介绍Teradata类型转换和格式化的迁移语法。迁移语法决定了关键字/特性的迁移方式。

在Teradata中，FORMAT关键词用于格式化字段/表达式。FORMAT '9(n)' 和'z(n)'分别使用0和空格（' '）填充，即使用LPAD函数。数据类型转换可通过CAST或直接指定数据类型实现：[like (expression1)(CHAR(n))]。DSC使用CAST完成。

DSC支持如下类型转换和格式化：

CHAR

输入：CHAR转换

1
 
(expression1)(CHAR(n))

输出

1
 
CAST( (expression1) AS CHAR(n) )

COLUMNS和COLUMN ALIAS

输入：对某列进行类型转换和格式化时，应确保列名和别名相同

1
2
3
4
5
6
7
 
SELECT Product_Line_ID, MAX(Standard_Price) 
  FROM ( SELECT A.Product_Description, A.Product_Line_ID
   , A.Standard_Price(DECIMAL(18),FORMAT '9(18)')(CHAR(18))  
        FROM product_t A
      WHERE Product_Line_ID in (1, 2) 
  ) AS tabAls 
 GROUP BY Product_Line_ID;

输出

1
2
3
4
5
6
7
 
SELECT Product_Line_ID, MAX( Standard_Price )
   FROM  ( SELECT A.Product_Description, A.Product_Line_ID
        , CAST( LPAD( CAST(A.Standard_Price AS DECIMAL( 18 ,0 )), 18, '0' ) AS CHAR( 18 ) ) AS Standard_Price 
                 FROM product_t A
               WHERE Product_Line_ID IN( 1 ,2 )
           ) AS tabAls
  GROUP BY Product_Line_ID;

表达式

输入：对表达式进行类型转换和格式化

1
2
3
 
SELECT product_id, standard_price*100.00(DECIMAL (17),FORMAT '9(17)' )(CHAR(17) ) AS order_amt 
   FROM db_pvfc9_std.Product_t 
  WHERE product_line_id is not null;

输出

1
2
3
 
SELECT product_id, CAST(LPAD(CAST(standard_price*100.00 AS DECIMAL(17)), 17, '0') AS CHAR(17)) AS order_amt 
   FROM db_pvfc9_std.Product_t 
   WHERE product_line_id is not null;

INT

输入：INT转换

1
2
3
4
5
6
 
SELECT
          CAST( col1 AS INT ) (
               FORMAT '9(5)'
          )
     FROM
          table1;

输出

1
2
3
4
 
SELECT
          LPAD( CAST( col1 AS INT ) ,5 ,'0' )
     FROM
          table1;

输入：INT转换

1
2
3
4
5
6
 
SELECT
          CAST( col1 AS INT ) (
               FORMAT '999999'
          )
     FROM
          table1;

输出

1
2
3
4
 
SELECT
          LPAD( CAST( col1 AS INT ) ,6 ,'0' )
     FROM
          table1;

输入：INT转换

1
2
3
4
 
SELECT
          CAST( expression1 AS INT FORMAT '9(10)' )
     FROM
          table1;

输出

1
2
3
4
 
SELECT
          LPAD( CAST( expression1 AS INT ) ,10 ,'0' )
     FROM
          table1;

输入：INT转换

1
2
3
4
 
SELECT
          CAST( expression1 AS INT FORMAT '9999' )
     FROM
          table1;

输出

1
2
3
4
 
SELECT
          LPAD( CAST( expression1 AS INT ) ,4 ,'0' )
     FROM
          table1;

DATE

在Teradata中对DATE进行格式转换时，使用AS FORMAT。DSC将添加TO_CHAR函数来保留指定的输入格式。

详情请参见日期和时间函数。

输入：数据类型转换，不使用DATE关键字

1
2
3
 
SELECT
      CAST( CAST( '2013-02-12' AS DATE FORMAT 'YYYY/MM/DD' ) AS FORMAT 'DD/MM/YY' )
;

输出

1
2
3
 
SELECT
      TO_CHAR( CAST( '2013-02-12' AS DATE ) ,'DD/MM/YY' )
;

转换数据类型DAY为SECOND

输入：DAY转换为SECOND

1
2
 
SELECT CAST(T1.Draw_Gold_Dt || ' ' ||T1.Draw_Gold_Tm as Timestamp)
- CAST(T1.Tx_Dt || ' '|| T1.Tx_Tm as Timestamp)  DAY(4) To SECOND  from db_pvfc9_std.draw_tab T1;

输出

1
2
3
4
5
 
SELECT
          CAST(( CAST( T1.Draw_Gold_Dt || ' ' || T1.Draw_Gold_Tm AS TIMESTAMP ) - CAST(T1.Tx_Dt || ' ' || T1.Tx_Tm AS TIMESTAMP ) ) AS INTERVAL DAY ( 4 ) TO SECOND )
     FROM
          db_pvfc9_std.draw_tab T1
;

DECIMAL

输入：DECIMAL转换

1
2
3
4
5
6
7
8
9
 
SELECT
          standard_price (
               DECIMAL( 17 )
               ,FORMAT '9(17)'
          ) (
               CHAR( 17 )
          )
     FROM
          db_pvfc9_std.Product_t;

输出

1
2
3
4
 
SELECT
          CAST( LPAD( CAST( standard_price AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS CHAR( 17 ) )
     FROM
          db_pvfc9_std.Product_t;

输入：DECIMAL转换

1
2
3
4
5
6
7
8
9
 
SELECT
          standard_price (
               DECIMAL( 17 ,0 )
               ,FORMAT '9(17)'
          ) (
               VARCHAR( 17 )
          )
     FROM
          db_pvfc9_std.Product_t;

输出

1
2
3
4
 
SELECT
          CAST( LPAD( CAST( standard_price AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS VARCHAR( 17 ) )
     FROM
          db_pvfc9_std.Product_t;

输入：DECIMAL转换

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT
          customer_id (
               DECIMAL( 17 )
          ) (
               FORMAT '9(17)'
          ) (
               VARCHAR( 17 )
          )
     FROM
          db_pvfc9_std.Customer_t;

输出

1
2
3
4
 
SELECT
          CAST( LPAD( CAST( customer_id AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS VARCHAR( 17 ) )
     FROM
          db_pvfc9_std.Customer_t;

时间间隔

DDL和DML支持将数据类型转换为时间间隔。用户可在VIEW、MERGE和INSERT子查询和SELECT内进行转换。

输入：转换为时间间隔

1
 
SELECT TIME '06:00:00.00' HOUR TO SECOND;

输出

1
 
SELECT TIME '06:00:00.00';

输入：转换为时间间隔，使用TOP

1
2
 
SELECT TOP 3 * FROM dwQErrDtl_mc.C03_CORP_AGENT_INSURE 
WHERE Data_Dt > (SELECT TIME '06:00:00.00' HOUR TO SECOND);

输出

1
 
SELECT  * FROM dwQErrDtl_mc.C03_CORP_AGENT_INSURE WHERE  Data_Dt > (SELECT TIME '06:00:00.00')   limit 3;

NULL

DSC将NULL(data_type)形式的表达式迁移为CAST(NULL AS replacement_data_type)。

输入：NULL转换

1
 
NULL(VARCHAR(n))

输出

1
 
CAST(NULL AS VARCHAR(n))

隐式类型转换

输入：隐式类型转换

 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
 
SELECT Data_Type,Start_Dt,End_Dt
 FROM (
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '101' AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-1 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  ) TT
  UNION ALL
  SELECT '201' AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-7 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  FROM Sys_Calendar.CALENDAR
  WHERE calendar_date = CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')
  AND Day_Of_Week = 1
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '401' AS Data_Type,CAST('${TX_PRIMONTH_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_MONTH_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '501' AS Data_Type,CAST('${TX_PRIQUARTER_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_QUARTER_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '701' AS Data_Type,CAST('${TX_PRIYEAR_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_YEAR_END}' AS DATE FORMAT 'YYYYMMDD')
 ) T1
 ;

输出

SELECT Data_Type,Start_Dt,End_Dt
 FROM (
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('101' AS TEXT) AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-1 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  ) TT
  UNION ALL
  SELECT CAST('201' AS TEXT) AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-7 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  FROM Sys_Calendar.CALENDAR
  WHERE calendar_date = CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')
  AND Day_Of_Week = 1
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('401' AS TEXT) AS Data_Type,CAST('${TX_PRIMONTH_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_MONTH_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('501' AS TEXT) AS Data_Type,CAST('${TX_PRIQUARTER_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_QUARTER_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('701' AS TEXT) AS Data_Type,CAST('${TX_PRIYEAR_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_YEAR_END}' AS DATE FORMAT 'YYYYMMDD')
 ) T1
 ;

十六进制字符字面值

输入

输出

 
1
2
3
 
'CASE WHEN Nullable='Y' THEN '' 
           ELSE ' NOT NULL' 
 END || '0A'XC

1
2
3
 
CASE WHEN Nullable='Y' THEN '' 
           ELSE ' NOT NULL' 
 END || E'\x0A'

Hexadecimal Character literal value

输入

输出

 
1
2
3
4
5
 
'SELECT CASE WHEN Nullable='Y' THEN '' 
           ELSE  NOT NULL 
 END || '0A'XC AS  SP_DATA_DT   
  FROM tbl_table; 
.IF ERRORCODE <> 0 THEN .QUIT 12

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
DECLARE lv_mig_errorcode NUMBER ( 4 ) ;
lv_mig_SP_DATA_DT TEXT ;
BEGIN
BEGIN
SELECT
          STRING_AGG (
                    CASE
                         WHEN Nullable = 'Y' THEN ''
                    ELSE NOT NULL
               END || E'\x0A' /* ????????? */
               ,','
          ) INTO
               lv_mig_SP_DATA_DT
          FROM
               tbl_table ;
lv_mig_errorcode := 0 ;
EXCEPTION
     WHEN OTHERS THEN lv_mig_errorcode := - 1 ;
END ;
IF lv_mig_errorcode <> 0 THEN RAISE EXCEPTION '12' ;
END IF ;
END ;
/

TRIM含INT类型转换

输入

输出

 
1
 
TRIM(columnlength (INT))

1
 
TRIM(mig_td_ext.mig_fn_castasint(columnlength))
父主题： Teradata语法迁移