Select failed 3810 Column / Parameter .....does not exist

Database
N/A

Select failed 3810 Column / Parameter .....does not exist

I inherited the code below and am trying to add 2 new fields to the results (bolded below).  The code I'm using to join is also bolded below.  I'm sure it is something I'm dong that is causing me to get the following error message when I run this sql.  The error message says: Select failed 3810 Column / Paramete rfs_rv.pft.paymt_mdia_proc_sys_cde does not exist.  I realized this field exists in the PAYMT database and not the RFS_RV database but I cannot figure out why I'm getting this message.  Any help would be greatly appreciated.  Thank you.

with dqry_cc (mop_cd, mop_desc, group_branch_id, ecr_tmz_name, stn_stn_id ,  stn_id, grp_brn_id  ) as

(select    

  a.mop_cd ,

    a.mop_desc,

    so.group_branch_id ,

    t.ecr_tmz_name,

    so.stn_stn_id,

    s.stn_id ,

    s.grp_brn_id

 from       

 rfs.stn_ops_hierarchies so,

 rfs.mthd_of_pymts a,

 rfs.stns s,

 rfs.tmzs_map t

 where s.stn_id   =     so.stn_stn_id

  and a.mpt_mop_type_code = 'CC'

  and s.TMZ = t.TMZS_TMZ

  and so.group_id = '01'

  and so.region_cd = 'RGN_stl_99'

  and so.group_branch_id ='0101')

select *

from

(select    

cc.group_branch_id as GpBr,

cc.mop_desc as Descr,

cast(SYSLIB.ERAC_GMT_TO_LCL(ph.pymt_dt, cc.ecr_tmz_name) AS timestamp(6)) as LocalPaydate,

cast(SYSLIB.ERAC_GMT_TO_LCL(ph.pymt_dt, cc.ecr_tmz_name) as date)  as ph_pymt_dt,

dr.dvr_srnm as DriverFName,

dr.dvr_frst_name as DriverLName,

ph.name as HolderName,

pd.ticket_no as ECARS2,

cast( case when rb.ecr_lgcy_resv_nbr  is null then

       case when substr(pd.ticket_no,1,1) = 'D'  then

          'D'||substr(pd.ticket_no, 2,6)

                       else

                null

                       end

     else   'D'||rb.ecr_lgcy_resv_nbr

   end  as CHAR(7)) as ECARS1,

rb.rnt_agr_nbr as RntAgrNo,

case when ph.cr_card_trans_typ_cde='R' then

   -1*pd.pymt_amt

  else

     pd.pymt_amt

  end   as AMOUNT, 

pd.cur_curr_cd as Curr,

ph.CR_CARD_NBR as CardNo,

cast(ph.exp_dt as date format 'mm/yy') as ExpireDate,

cast(0 as decimal(15,3)) as AUTH_AMT,

cast(null as date format 'mm/dd/yyyy') as AuthDate,

ph.auth_nbr as AuthNo,

cast(null as char(2)) as Swipe,

cast(null as varchar(60)) as PType,

 pft.paymt_mdia_proc_sys_cde as Settlement,

 pft.prim_acct_frst_six_dgt_nbr as First_Six

from

dqry_cc  cc,  

rfs_rv.pre_applied_pymts_hdr ph,

rfs_rv.pre_applied_pymts_det pd

left outer join

 (select       

  ra.rnt_agr_nbr,

  ra.ecr_ticket_no,

  ra.ecre_rent_cntrct_nbr,

  ra.ecr_lgcy_resv_nbr,

  cc.grp_brn_id --

 from            

 rfs_rv.rnt_agrs ra,

 dqry_cc  cc

 where   ra.sta_stn_id_orig_co = cc.stn_id QUALIFY ROW_NUMBER() OVER(PARTITION BY  ra.rnt_agr_nbr ORDER BY ra.rnt_agr_nbr) = 1

 ) rb

    on pd.ticket_no = rb.ecr_ticket_no

 left outer join

     (select       

          dvr.dvr_srnm,

            dvr.dvr_frst_name,

            dvr.rdy_rnt_agr_nbr

     from rfs_rv.dvr_rras dvr

     where dvr.main_dvr_flg = 'MR'

     ) dr

     on rb.rnt_agr_nbr = dr.rdy_rnt_agr_nbr

 left outer join 

  (select

  ft.paymt_mdia_proc_sys_cde as Settlement,

   ft.prim_acct_frst_six_dgt_nbr as First_Six

    from paymt.fin_tran ft) pft

     on pft.fin_tran_ref_id =  cast(ph.paph_fin_trans_ref_id as decimal(19,0))


 where ph.pymt_stn_id = cc.stn_stn_id

 and ph.mop_mop_cd = cc.mop_cd

    and ph.pymt_id = pd.pap_pymt_id

    and ph_pymt_dt  = 8/5/2015

) z

Tags (2)
2 REPLIES
Teradata Employee

Re: Select failed 3810 Column / Parameter .....does not exist

You already renamed paymt_mdia_proc_sys_cde as Settlement in the inner query (pft). So you need to use that name in the outer query.

N/A

Re: Select failed 3810 Column / Parameter .....does not exist

@Fred..........I've made the change you suggested (or at least what I thought you were suggesting) and I'm now getting a "Error 3782 Improper column reference in the search condition of a joined table".  I've highlighted the changes I made.  Thanks for your help.  I'm an accountant struggling with sql and really appreciate your assistance.

with dqry_cc (mop_cd, mop_desc, group_branch_id, ecr_tmz_name, stn_stn_id ,  stn_id, grp_brn_id  ) as

(select   

   a.mop_cd ,

     a.mop_desc,

     so.group_branch_id ,

     t.ecr_tmz_name,

     so.stn_stn_id,

     s.stn_id ,

     s.grp_brn_id

  from      

  rfs.stn_ops_hierarchies so,

  rfs.mthd_of_pymts a,

  rfs.stns s,

  rfs.tmzs_map t

 where s.stn_id   =     so.stn_stn_id

   and a.mpt_mop_type_code = 'CC'

   and s.TMZ = t.TMZS_TMZ

   and so.group_id = '01'

   and so.region_cd = 'RGN_stl_99'

   and so.group_branch_id ='0101')

select *

from

(select   

 cc.group_branch_id as GpBr,

 cc.mop_desc as Descr,

 cast(SYSLIB.ERAC_GMT_TO_LCL(ph.pymt_dt, cc.ecr_tmz_name) AS timestamp(6)) as LocalPaydate,

 cast(SYSLIB.ERAC_GMT_TO_LCL(ph.pymt_dt, cc.ecr_tmz_name) as date)  as ph_pymt_dt,

 dr.dvr_srnm as DriverFName,

 dr.dvr_frst_name as DriverLName,

 ph.name as HolderName,

 pd.ticket_no as ECARS2,

cast( case when rb.ecr_lgcy_resv_nbr  is null then

        case when substr(pd.ticket_no,1,1) = 'D'  then

           'D'||substr(pd.ticket_no, 2,6)

                        else

                 null

                        end

      else   'D'||rb.ecr_lgcy_resv_nbr

    end  as CHAR(7)) as ECARS1,

 rb.rnt_agr_nbr as RntAgrNo,

case when ph.cr_card_trans_typ_cde='R' then

    -1*pd.pymt_amt

   else

      pd.pymt_amt

   end   as AMOUNT,

pd.cur_curr_cd as Curr,

 ph.CR_CARD_NBR as CardNo,

cast(ph.exp_dt as date format 'mm/yy') as ExpireDate,

 cast(0 as decimal(15,3)) as AUTH_AMT,

 cast(null as date format 'mm/dd/yyyy') as AuthDate,

ph.auth_nbr as AuthNo,

cast(null as char(2)) as Swipe,

 cast(null as varchar(60)) as PType,

 pft.Settlement,

 pft.First_Six


 from

 dqry_cc  cc, 

 rfs_rv.pre_applied_pymts_hdr ph,

 rfs_rv.pre_applied_pymts_det pd

left outer join

  (select      

   ra.rnt_agr_nbr,

   ra.ecr_ticket_no,

   ra.ecre_rent_cntrct_nbr,

   ra.ecr_lgcy_resv_nbr,

   cc.grp_brn_id --

 from           

  rfs_rv.rnt_agrs ra,

  dqry_cc  cc

  where   ra.sta_stn_id_orig_co = cc.stn_id QUALIFY ROW_NUMBER() OVER(PARTITION BY  ra.rnt_agr_nbr ORDER BY ra.rnt_agr_nbr) = 1

  ) rb

     on pd.ticket_no = rb.ecr_ticket_no

 left outer join

      (select      

           dvr.dvr_srnm,

             dvr.dvr_frst_name,

             dvr.rdy_rnt_agr_nbr

      from rfs_rv.dvr_rras dvr

      where dvr.main_dvr_flg = 'MR'

      ) dr

      on rb.rnt_agr_nbr = dr.rdy_rnt_agr_nbr

 left outer join

   (select

    ft.paymt_mdia_proc_sys_cde as Settlement,

    ft.prim_acct_frst_six_dgt_nbr as First_Six,

    ft.fin_tran_ref_id

     from paymt.fin_tran ft) pft

      on pft.fin_tran_ref_id =  cast(ph.paph_fin_trans_ref_id as decimal(19,0))

  where ph.pymt_stn_id = cc.stn_stn_id

  and ph.mop_mop_cd = cc.mop_cd

     and ph.pymt_id = pd.pap_pymt_id

     and ph_pymt_dt  = 8/5/2015

) z