文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Teradata语法迁移> 类型转换和格式化
查看PDF

类型转换和格式化

分享
更新时间： 2019/12/24 GMT+08:00

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

在Teradata中，FORMAT关键词用于格式化字段/表达式。格式'9(n)' 和'z(n)'分别使用带有0和空格（' '）的LPAD表示。数据类型转换可使用CAST或直接数据类型转换方式[like (expression1)(CHAR(n))]。Migration Tool的此功能使用CAST完成。

Migration Tool支持如下类型转换和格式化语句：

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
7
 
SELECT
          CAST( col1 AS INT ) (
               FORMAT '9(5)'
          )
     FROM
          table1
;

输出

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

输入：转换数据类型为INT

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

输出

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

输入：转换数据类型为INT

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

输出

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

输入：转换数据类型为INT

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

输出

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

DATE

在Teradata中对DATE进行格式转换时，使用AS FORMAT。Migration Tool将添加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
10
 
SELECT
          standard_price (
               DECIMAL( 17 )
               ,FORMAT '9(17)'
          ) (
               CHAR( 17 )
          )
     FROM
          db_pvfc9_std.Product_t
;

输出

1
2
3
4
5
 
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
10
 
SELECT
          standard_price (
               DECIMAL( 17 ,0 )
               ,FORMAT '9(17)'
          ) (
               VARCHAR( 17 )
          )
     FROM
          db_pvfc9_std.Product_t
;

输出

1
2
3
4
5
 
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
11
 
SELECT
          customer_id (
               DECIMAL( 17 )
          ) (
               FORMAT '9(17)'
          ) (
               VARCHAR( 17 )
          )
     FROM
          db_pvfc9_std.Customer_t
;

输出

1
2
3
4
5
 
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

Migration Tool会将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
 ;

输出

 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 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
 ;
父主题： Teradata语法迁移
分享：

    相关文档

    相关产品

文档是否有解决您的问题？

提交成功！

非常感谢您的反馈，我们会继续努力做到更好！

反馈提交失败，请稍后再试！

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区