teradata 字符截取，日期转变的部分查询代码

下面的sql语句可以体现部分teradata的函数，保留下来，可以供自己以后使用。

Create    Table data_tmp.dwt_file_agt_arriveddate , no Fallback,
  no Before journal,
  no After journal,
  checksum = Default
(
  Tx_Date Date format 'YYYY-MM-DD' Title '业务日期',
  Groupname char(30) Character Set latin Not casespecific Title '调度组',
  Fw_jobname char(40) Character Set latin Not casespecific Title 'FW 作业名',
  source_filename char(50) Character Set latin Not casespecific Title '源文件名',
  File_arrivedtime char(20) Character Set latin Not casespecific Title '实际文件到达时间',
  source_code char(2) Character Set latin Not casespecific Title '源系统代码'
)
Primary Index(source_filename);
Insert    Into dwpdata.dwt_file_agt_arriveddate
Select     Distinct a.Tx_date , c.groupname,b.job_name,
        Case
            When    Index(b.job_name,'-') >0
Then Substr(b.job_name,Index(b.job_name,'-')+1)
            When    Index(b.job_name,'-') =0
Then Substr(b.job_name,Index(b.job_name,'_')+1)
        End    ,
Cast(a.EndRun_Dt As char(20)),
        Case
            When    Index(b.job_name,'-') >0
     Then Substr(b.job_name,Index(b.job_name,'-')+1,2)
            When    Index(b.job_name,'-') =0
     Then Substr(b.job_name,Index(b.job_name,'_')+1,2)
        End
From    dwmdstmp.m05_etl_job_log a
Inner Join dwmdstmp.m05_etl_job b
    On    a.etl_job_id = b.etl_job_id
Inner Join dwmdstmp.ORA_ETL_JOB c
    On    b.job_name=c.jobname
Where    a.tx_Date >=date'2009-07-01'
    And    b.job_name Like '%FW%';