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.....
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,
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 ******
rfs.stns s on ph.pymt_stn_id = s.stn_id
rfs.mthd_of_pymts mp on ph.mop_mop_cd = mp.mop_cd
rfs_rv.pre_applied_pymts_det pd on ph.pymt_id = pd.pap_pymt_id
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 (
sto.grp_brn_id as ChkOutStn,
sti.grp_brn_id as ChkInStn,
rfs.stns sto on ra.sta_stn_id_orig_co = sto.stn_id
rfs.stns sti on ra.sta_stn_id_orig_co = sti.stn_id
rfs_rv.dvr_rras dr on ra.rnt_agr_nbr = dr.rdy_rnt_agr_nbr
dr.main_dvr_flg = 'MR'
) rb on pd.ticket_no = rb.ecr_ticket_no
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 = ?
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.
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.
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...