All Forums Database
gfuller40 24 posts Joined 05/10
13 Aug 2012
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
gfuller40 24 posts Joined 05/10
13 Aug 2012

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
ulrich 816 posts Joined 09/09
13 Aug 2012

And what does these error codes imply?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.