文档首页/ 数据仓库服务 DWS/ 工具指南/ DSC/ Teradata语法迁移/ Teradata格式
更新时间：2024-12-10 GMT+08:00
查看PDF
分享

Teradata格式

以YYYYMMDD格式输入的日期

输入

输出

 
1
2
3
 
SELECT 1 FROM tb_dt_fmtyyyymmdd 
WHERE JobName ='${JOB_NAME}' 
AND TXDATE = ${TX_DATE} - 19000000;

SELECT 1 FROM tb_dt_fmtyyyymmdd 
WHERE JobName ='${JOB_NAME}' 
AND TXDATE = TO_DATE(${TX_DATE}, 'YYYYMMDD');

以YYYYDDD格式输入日期

输入

输出

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
REPLACE VIEW SC.VIEW_1 
 ( 
 col_1 
 ) 
 LOCKING TABLE sc.tab FOR ACCESS 
 AS 
 SEL 
 --tgt.col_1 is date type 
 CAST( CAST(TGT.col_1 AS DATE FORMAT 'YYYYDDD') AS CHAR(7) ) AS col_1 
 FROM 
 sc.tab TGT 
 ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE 
      OR REPLACE VIEW SC.VIEW_1 (col_1)  
     /*LOCKING TABLE sc.tab FOR ACCESS */ 
      AS ( 
           SELECT 
                     /* tgt.col_1 is date type */ 
                     CAST( TO_DATE(TGT.col_1, 'YYYYDDD') AS CHAR( 7 ) ) AS col_1 
                FROM 
                     sc.tab TGT 
      ) ;

以#开头的列名

输入

输出

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
REPLACE VIEW SC.VIEW_1 
 ( 
 ,col_1 
 ,#_col_2 
 ,#_col_3 
 ) 
 LOCKING TABLE sc.tab FOR ACCESS 
 AS 
 SEL 
 Tgt.col1 
 ,Tgt.#_col_2 
 ,Tgt.#_col_3 
 FROM 
 sc.tab TGT 
 ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE 
      OR REPLACE VIEW SC.VIEW_1 ( 
           ,col_1 
           ,"#_COL_2" 
           ,"#_COL_3" 
      ) /*LOCKING TABLE sc.tab FOR ACCESS */ 
      AS ( 
           SELECT 
                     Tgt.col1 
                     ,Tgt."#_COL_2" 
                     ,Tgt."#_COL_3" 
                FROM 
                     sc.tab TGT 
      ) ;

类型转换时优先执行数据库操作

输入

输出

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
REPLACE VIEW SC.VIEW_1 
 ( 
 col_1 
 ) 
 LOCKING TABLE sc.tab FOR ACCESS 
 AS 
 SEL 
 (COALESCE(TRIM(TGT.col_1),'')) || '_' || (COALESCE(TRIM(TGT.col_1),'')) (CHAR(22)) AS col_1 
 FROM 
 sc.tab TGT 
 ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
CREATE 
      OR REPLACE VIEW SC.VIEW_1 (col_1)  
     /*LOCKING TABLE sc.tab FOR ACCESS */ 
      AS ( 
           SELECT 
                    CAST(  
                     ( COALESCE( TRIM( TGT.col_1 ) ,'' ) ) || '_' || ( COALESCE( TRIM( TGT.col_1 ) ,'' ) ) 
                    AS CHAR( 22 ) ) AS col_1 
                FROM 
                     sc.tab TGT 
      ) ;
父主题： Teradata语法迁移

相关文档