Date Diff function/ column not found error.

Database
Enthusiast

Date Diff function/ column not found error.

I'm trying to do a datediff function that grans the total # of days between todays date and a prior payment date. For some reason I'm getting an error stating that the "pmnt_rlse_dt" column is not found.

select ee.alias_last_nm || ', ' || ee.alias_first_nm as "Custodian"
,clm.i_sys_clm
,clm.c_clm as "Claim Number"
,clm.i_pol as "Policy Number"
,clm.n_lst as "Last Name"
,clm.n_fst as "First Name"
,clm.c_sta_clm as "Status"
,mx_dt as "Last Payment Date"
,current_date - pmnt_rlse_dt as "Days Since Paid"
from ltc_p.vltc_clm_mo clm

left join (select indiv_sorce_syst_cd
,sorce_upc_indiv_id
,alias_last_nm
,alias_first_nm
from edw_p.upc_indiv_alias_v2
where row_end_dt = '9999-12-31' and
indiv_sorce_syst_cd = 'ORG') ee
on clm.c_ams_clm = ee.sorce_upc_indiv_id

inner join(select sorce_claim_id
,max(pmnt_rlse_dt) as mx_dt
from ltc_p.claim_pmnt pp
group by 1
having max(pmnt_rlse_dt) is not null) as d
on clm.i_sys_clm = d.sorce_claim_id

where clm.c_sta_clm = 'AC'
order by 1,2

2 REPLIES
Enthusiast

Re: Date Diff function/ column not found error.

Ooops I've also tried below which give me "selected non aggregate functions must be a part of the associated group"

inner join(select sorce_claim_id
,max(pmnt_rlse_dt) as mx_dt
,current_date - pmnt_rlse_dt as "Days Since Paid"
from ltc_p.claim_pmnt

Supporter

Re: Date Diff function/ column not found error.

And what does these error codes imply?