All Forums Database
shaves 22 posts Joined 04/15
10 Aug 2015
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

Fred 1096 posts Joined 08/04
10 Aug 2015

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.

shaves 22 posts Joined 04/15
11 Aug 2015

@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

You must sign in to leave a comment.