Error 3782 Improper column reference in the search condiditon of a joined table

Database
Enthusiast

Error 3782 Improper column reference in the search condiditon of a joined table

I'm geting an error 3782 when trying to add the last 2 fields in the first selet statement and I don't know what is wrong.  Any help would be appreciated.  Thanks.....

select

    mp.mop_desc as "Card Type",

    ph.CR_CARD_NBR  as "CC Last 4",

    ph.name as Card_Holder,

    cast(ph.pymt_dt as date format 'mm/dd/yyyy') as PayDt,

    ph.pymt_amt,

    s.grp_brn_id as CC_Charge_GPBR,

    rb.rnt_agr_nbr as Rent_Agr,

    rb.chkoutstn as Renting_GPBR,

    rb.chkinstn as Close_GPBR,

    cast(rb.co_tmsp as date format 'mm/dd/yyyy') as Check_Out,

    cast(rb.ci_tmsp as date format 'mm/dd/yyyy') as Check_In,

    rb.ecr_lgcy_resv_nbr as Ralph#,

    rb.ecr_ticket_no as ECARS2#,

    rb.dvr_frst_name as Driver_First,

    rb.dvr_srnm as Driver_Last,

    cast(ph.paph_fin_trans_ref_id as decimal(19,0)) as refid,

    fin_tran.paymt_mdia_proc_sys_cde as Settlement, *****

    fin_tran.prim_acct_frst_six_dgt_nbr as First_Six ******

from

    rfs_rv.pre_applied_pymts_hdr ph

join

    rfs.stns s on ph.pymt_stn_id = s.stn_id

join

    rfs.mthd_of_pymts mp on ph.mop_mop_cd = mp.mop_cd

join

    rfs_rv.pre_applied_pymts_det pd on ph.pymt_id = pd.pap_pymt_id

join

    paymt.fin_tran ft on fin_tran.fin_tran_ref_id =cast(ph.paph_fin_trans_ref_id as decimal(19,0))

left outer join (

    select

       ra.rnt_agr_nbr,

       ra.ecr_ticket_no,

       ra.ecre_rent_cntrct_nbr,

       ra.ecr_lgcy_resv_nbr,

       ra.co_tmsp,

       ra.ci_tmsp,

       sto.grp_brn_id as ChkOutStn,

       sti.grp_brn_id as ChkInStn,

       dr.dvr_srnm,

       dr.dvr_frst_name

    from

       rfs_rv.rnt_agrs ra

    join

       rfs.stns sto on ra.sta_stn_id_orig_co = sto.stn_id

    join

       rfs.stns sti on ra.sta_stn_id_orig_co = sti.stn_id

    join

       rfs_rv.dvr_rras dr on ra.rnt_agr_nbr = dr.rdy_rnt_agr_nbr

    where

        dr.main_dvr_flg = 'MR'

) rb on pd.ticket_no = rb.ecr_ticket_no

where

mp.mop_desc = ?

and ph.CR_CARD_NBR  = ?

and ph.pymt_dt  between '2015-05-30 00:00:00' and '2015-06-26 23:59:59'

and ph.cust_nbr = ?

Tags (2)
4 REPLIES
Enthusiast

Re: Error 3782 Improper column reference in the search condiditon of a joined table

Hard to be sure without being able to explain/run the sql but it looks like you have

paymt.fin_tran aliased as ft and you are refering to it in you select list as fin_tran.

Rglass

Teradata Employee

Re: Error 3782 Improper column reference in the search condiditon of a joined table

And in the ON condition: fin_tran.fin_tran_ref_id instead of ft.fin_tran_ref_id

This one is where the error is coming from. Once you fix that, then the one RGlass pointed out in the select list would ad an unconstrained join of fin_tran to your query making it likely to get incorrect answers and run a very long time.

Enthusiast

Re: Error 3782 Improper column reference in the search condiditon of a joined table

Thank you for your advice.  That did the trick. 

Enthusiast

Re: Error 3782 Improper column reference in the search condiditon of a joined table

ToddWalter.....I saw that when I made the change suggested by Rglass and fixed that.  The query just ran and returned the results I was looking for...