SELECT   a.RECIP_SSN_NBR,
     
         char(b.recip_retir_dt, USA), 
         sum(a.OBLIG_TOT_AMT)  AS TOTAL 
         year(a.OBLIG_CREAT_DT) 
         
         
FROM     DSNP.PR01_T_OBLIG_CMPNT a, 
         DSNP.PR01_T_RECIP_SYS b 
      
         
         
WHERE    a.RECIP_SSN_NBR=b.RECIP_SSN_NBR 




AND      a.CMPNT_TYPE_CD = '42' 
AND      YEAR(a.OBLIG_CREAT_DT) IN (2009, 2010, 2011)
and      b.benef_stat_cd in ('AC', 'DP')
and      b.recip_retir_dt2 is NULL
and      b.benef_seq_nbr = 1


GROUP BY 

a.RECIP_SSN_NBR,

         b.recip_retir_dt,  
year(a.OBLIG_CREAT_DT)

quote:

---

Originally posted by TWD38

SQL is telling me that my use of the YEAR function is not valid.
Can anyone tell me why? How can I query for multiple years?

Here is my query:

SELECT   a.RECIP_SSN_NBR,
     
         char(b.recip_retir_dt, USA), 
         sum(a.OBLIG_TOT_AMT)  AS TOTAL 
         year(a.OBLIG_CREAT_DT) 
         
         
FROM     DSNP.PR01_T_OBLIG_CMPNT a, 
         DSNP.PR01_T_RECIP_SYS b 
      
         
         
WHERE    a.RECIP_SSN_NBR=b.RECIP_SSN_NBR 




AND      a.CMPNT_TYPE_CD = '42' 
AND      YEAR(a.OBLIG_CREAT_DT) IN (2009, 2010, 2011)
and      b.benef_stat_cd in ('AC', 'DP')
and      b.recip_retir_dt2 is NULL
and      b.benef_seq_nbr = 1


GROUP BY 

a.RECIP_SSN_NBR,

         b.recip_retir_dt,  
year(a.OBLIG_CREAT_DT)

The query works if I use = to a specific year, but with multiple years.

---