更新时间:2024-04-18 GMT+08:00
Teradata格式
以YYYYMMDD格式输入的日期
输入 |
输出 |
|
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
;
|
|
|
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
) ;
|
|
类型转换时优先执行数据库操作
输入 |
输出 |
|
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
;
|
|
|
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
) ;
|
|