文档首页/ 数据仓库服务 GaussDB(DWS)/ 更多文档/ 工具指南（吉隆坡地区）/ DSC SQL语法迁移工具/ Teradata语法迁移/ 类型转换和格式化
更新时间：2023-03-17 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
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。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
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

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
 ;

输出

 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语法迁移