Will This Case Statement Work??

[lrdave36]

Hey guys,

Is it possible to insert a SELECT statement inside of a CASE statment? The logic looks right to me, but I get syntax errors when running it. I just can't spot anything wrong this code. Is this structure even allowed in SQL? I can't find any other way to use the MIN function without creating a sub-select.

SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
        
        
         sum(case when A.RECIP_RETIR_DT <= '2010-04-01' then 1 end)   AS "TOTAL RETIREES", 

         sum(case when recip_retir_dt2 = '2010-05-01' and A.BENEF_SEQ_NBR = 1 then 1 end)  AS "NEW DROPS", 

         sum(case when A.BENEF_STAT_CD = 'AC' and RECIP_TYPE_CD in ('10') and  RECIP_RETIR_DT BETWEEN '2010-03-01' and '2010-03-30' then 1 end)   AS "A&S RETIREES", 

[b]
         sum(case When RIGHT (VOUCHER_ID_CD,1) = 'D' and B.ANTY_PYMT_DT = (select A.recip_ssn_nbr from dsnp.pr01_t_anty_pymt A, 
             dsnp.pr01_t_recip_sys B 
         
             where A.recip_ssn_nbr = B.recip_ssn_nbr 
             group by A.recip_ssn_nbr 
             having min(A.anty_pymt_dt) = '2010-05-01') then 1 end)   AS "DISABILITY RETIREES" 
        
[/b]    
  



FROM     DSNP.PR01_T_RECIP_SYS A, 
         DSNP.PR01_T_ANTY_PYMT B 
WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
AND      A.BENEF_STAT_CD IN ('AC', 'DP') 
AND      A.RECIP_TYPE_CD = '10' AND      A.`BENEF_SEQ_NBR = 1 
GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD 
ORDER BY 3 DESC

The section in question is the part for Disability Retirees.

 

[SQLBill]

You haven't posted the error messages. When you click on the error message it should highlight the portion of the code where the error is or at least close to it (it's not perfect). Knowing that could help us troubleshoot this.

Also, do you have QUOTED_IDENTIFIER option set to ON? Try adding this to the beginning of your script:

SET QUOTED_IDENTIFIER ON
GO
<rest of script>

If that solves the issue, then the double quotes (") are your 'syntax' error. Change them to square brackets or set the QUOTED_IDENTIFIER option to on.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875