Archives of the TeradataForum

Message Posted: Wed, 07 Nov 2007 @ 19:23:32 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have to extract time from a numeric field. I am using the below logic(highlighted) . But am getting error:

Syntax error 3707:Expected something like "||" or a FROM keyword between ')' and '('.

       SELECT drv.acct_id ACCT_ID,
       0,
     -- '22:33:44' as arrmt_made_tm,
     cast(substring((trim(drv.DT_TM_NUM))(char(25)) from 9 for 6) as time(6)) abc,
              drv.arr_made_dt ARR_MADE_DT,
              coalesce(drv.arr_due_dt,cast('1900-01-01' as date format
     'YYYY-MM-DD')) ARR_DUE_DT,
              fin_hier.ACCT_FIN_HIER_DIM_ID ACCT_FIN_HIER_DIM_ID,
              trtmt.colln_acct_trtmt_dim_id COLLN_ACCT_TRTMT_DIM_ID,
              stat.colln_acct_stat_dim_id COLLN_ACCT_STAT_DIM_ID,
              coalesce((case when substring(vol2.evt_detl_tag_val from 1 for 1)
     not in ('I','O') then 'U' else substring(vol2.evt_detl_tag_val from 1 for 1)
       end),'U') call_type_Cd,
              drv.ACPT_AGT_ID ACPT_AGT_ID,
              'P' CLSG_AGT_ID,
              '*2' as ARRMT_STAT_CD,
              NULL,
              coalesce(cdv.acct_score_decl_cd,0) ACCT_SCORE_DECL_CD,
              drv.arr_cnt ARRMT_CNT,
              drv.ptp_cnt as PTP_CNT,
              coalesce( drv.PTP_AMT,0),

              20070908 as edw_publn_id
         FROM (SELECT acct_id,
                arr_made_dt,
                  dt_tm_num,
               arr_due_dt,
               ptp_amt,
           acpt_agt_id,
           arr_cnt,
           ptp_cnt
             FROM arrmt_fact_vol_1) drv
          --------trtmt_dim_id
        LEFT JOIN ( SELECT acct_id,

                 --snap_dt,
                 new_dim_id1 colln_acct_trtmt_dim_id
             FROM pl_colln_trtmt_stg
                ) trtmt
             ON drv.acct_id=trtmt.acct_id

             --------stat_dim_id
          LEFT JOIN ( SELECT acct_id,
                   --snap_dt,
                    new_dim_id1 colln_acct_stat_dim_id
                      FROM acct_stat_stg
              ) stat
               ON drv.acct_id=stat.acct_id


              ----fin_hier_dim_id
             LEFT JOIN (  SELECT acct_id,
                    new_dim_id1 ACCT_FIN_HIER_DIM_ID
                  FROM pl_card_acct_fin_hier_stg
         ) fin_hier
         ON drv.acct_id=fin_hier.acct_id

             LEFT JOIN (SELECT ACCT_ID,
                               evt_detl_tag_val
                          FROM arrmt_fact_vol_2) vol2
                    ON drv.acct_id=vol2.acct_id
             LEFT JOIN (SELECT acct_id,
                              max(decile) acct_score_decl_cd
                          FROM care_decile_vol
                      group by 1
                          )cdv
                     ON drv.acct_id=cdv.acct_id

But when I use the same statement alone from drv table, I dont get any errors. I am new to Teradata. Please help.

Thanking you

	<Prev	Next>		<<First	<Prev	Next>	Last>>