Why is My Query Failing?

[VAST39]

Hey guys,

I am trying to run the following query, and can't see why it is giving me an error on the last line:

select A.MBR_SSN_NBR,
A.MBR_F_NM,
A.MBR_L_NM
A.MBR_STAT_CD,
A.AGTY_ID_CD,
A.MBR_HIST_SVC_CR_DT,
a.TOTAL


FROM


(SELECT distinct A.MBR_SSN_NBR,
C.MBR_F_NM,
C.MBR_L_NM,
A.MBR_STAT_CD,
B.AGTY_ID_CD,
B.MBR_HIST_SVC_CR_DT,
SUM(b.mbr_cntrb_amt) as TOTAL



FROM DSNP.PR01_T_MBR_SYS A,
DSNP.PR01_T_MBR_HIST B,
DSNP.PR01_T_MBR C



WHERE A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND A.MBR_SSN_NBR=C.MBR_SSN_NBR
AND B.EMPR_PLAN_CD in ('K', 'C')


GROUP BY A.MBR_SSN_NBR,
C.MBR_F_NM,
C.MBR_L_NM,
A.MBR_STAT_CD,
B.AGTY_ID_CD,
B.MBR_HIST_SVC_CR_DT) as A


WHERE A.MBR_HIST_SVC_CR_DT = (select max(A.mbr_hist_svc_cr_dt) from dsnp.pr01_t_mbr_hist B where b.mbr_ssn_nbr = A.mbr_ssn_nbr)

Basically I'm trying to accomplish two things here:

Pull their most recent service date which is stored in mbr_hist_svc_cr_dt

Get a grand total of their contribution amounts stored in mbr_cntrb_amt



I can't do this all inside one query because if I do the max on the service date then the total contribution is only showing the amount occurring on that max date record. The inside A table works by giving me their total contribution amount, but it shows me every service date for each SSN. I need the outside query to pick the most recent date and show only one record per person.

What am I missing here? Thanks in advance!

 

[VAST39]

Opps I just saw the problem. I had the wrong table alias reference inside MAX. Please disregard this post.

 

[fredericofonseca]

A.mbr_hist_svc_cr_dt is not part of the table being queried on the last subquery - you need to reference a field from table aliased as B.

also you are using a distinct on a group by expression - wrong as group by will on its own remove duplicates.

in any case the following may give you what you need

(SELECT A.MBR_SSN_NBR
       , C.MBR_F_NM 
       , C.MBR_L_NM
       , A.MBR_STAT_CD
       , B.AGTY_ID_CD
       , B.MBR_HIST_SVC_CR_DT
       , SUM(b.mbr_cntrb_amt) as TOTAL
FROM DSNP.PR01_T_MBR_SYS A
inner join DSNP.PR01_T_MBR_HIST B
on A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND B.EMPR_PLAN_CD in ('K', 'C')
inner join DSNP.PR01_T_MBR C
on A.MBR_SSN_NBR=C.MBR_SSN_NBR
inner join (select mbr_ssn_nbr
                 , max(mbr_hist_svc_cr_dt) as mbr_hist_svc_cr_dt
            from  dsnp.pr01_t_mbr_hist
            group by mbr_ssn_nbr
           ) t
on t.mbr_ssn_nbr = a.mbr_ssn_nbr
and t.mbr_hist_svc_cr_dt = mbr_hist_svc_cr_dt
GROUP BY A.MBR_SSN_NBR
        ,C.MBR_F_NM
        ,C.MBR_L_NM
        ,A.MBR_STAT_CD
        ,B.AGTY_ID_CD
        ,B.MBR_HIST_SVC_CR_DT

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

I think you figured out several errors already.

Most strikingly:

(SELECT distinct A.MBR_SSN_NBR,
C.MBR_F_NM,
C.MBR_L_NM,
A.MBR_STAT_CD,
B.AGTY_ID_CD,
B.MBR_HIST_SVC_CR_DT,
SUM(b.mbr_cntrb_amt) as TOTAL

This is not a complete subquery. You specify tables for the aliases A,B, and C later, too late.

Then later on, quite at the end you have a double use of the alias A. That may be possible, but is at least misleading. It's ok to shorten table names, but you could keep the aliases more descriptive and make them unique, even if it's inner and outer subquery aliases.

Bye, Olaf.