dws
类型转换和格式化
更新时间:2020/04/21 GMT+08:00
本节主要介绍Teradata类型转换和格式化的迁移语法。迁移语法决定了关键字/特性的迁移方式。
在Teradata中,FORMAT关键词用于格式化字段/表达式。FORMAT '9(n)' 和'z(n)'分别使用0和空格(' ')填充,即使用LPAD函数。数据类型转换可通过CAST或直接指定数据类型实现:[like (expression1)(CHAR(n))]。DSC使用CAST完成。
DSC支持如下类型转换和格式化:
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语法迁移
