adding additional fields to a query

Question

I'm trying to add 2 fields to an existing query and am having some difficulty.

These are the 2 fields I want to add:

paymt_mdia_proc_sys_cde as Settlement, prim_acct_frst_six_dgt_nbr as First_Six

from the ft.fin_tran table

I should join on the ft.fin_tran_ref_id field and the ph.refid field.

I cannot get this to work. I've tried the join and different places and can't get anywhere. I'm relatively new to writing sql and struggle with it. This query was designed by someone else and I was tasked with adding these 2 fields. I thought it would be pretty simple but it is not turning out that way. If you have any ideas on what I could try, I'd appreciate it. Thanks for the help.....

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

from 
rfs_rv.pre_applied_pymts_hdr ph,
rfs.stns s,
rfs.mthd_of_pymts mp,
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,
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,
rfs.stns sto,
rfs.stns sti,
rfs_rv.dvr_rras dr

where ra.sta_stn_id_orig_co = sto.stn_id
and ra.sta_stn_id_orig_co = sti.stn_id
and ra.rnt_agr_nbr = dr.rdy_rnt_agr_nbr
and dr.main_dvr_flg = 'MR') rb
on pd.ticket_no = rb.ecr_ticket_no

where ph.pymt_stn_id = s.stn_id
and ph.mop_mop_cd = mp.mop_cd
and ph.pymt_id = pd.pap_pymt_id
and mp.mop_desc = ?
and ph.CR_CARD_NBR  = ?
and ph.pymt_dt  between cast(? as date format 'mm/dd/yyyy') and cast(? as date format 'mm/dd/yyyy')
and ph.cust_nbr = ?

Answer 1

Mixing implicit and explicit joins isn't a good idea, so in addition to adding the new columns you needed I changed all joins to be explicit.

As you didn't supply any test data this might work, or it might not.

Maybe this is what you want?

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 
    ft.fin_tran fin_tran on fin_tran.fin_tran_ref_id = ph.refid 
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 cast(? as date format 'mm/dd/yyyy') and cast(? as date format 'mm/dd/yyyy')
and ph.cust_nbr = ?