Teradata Formats

Date in the YYYYMMDD Format

Input

Output

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');

Date in the YYYYDDD Format

Input

Output

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
      ) ;

Column Names Starting with #

Input

Output

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
 ;
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
      ) ;

Database Operations First During Type Casting

Input

Output

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
      ) ;