Will This Case Statement Work?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Will This Case Statement Work??

Author

Topic

sqlnoob35
Starting Member

10 Posts

Posted - 2010-05-11 : 12:36:32

I am new to CASE statements and trying to see if there is a way to insert a select statement inside the case statement.

The reason being is I have to use the min() function which requires using the HAVING CLAUSE. I can't just use a simple WHERE statement like I have with my other CASE statements.

Here is the code I am trying:



SELECT   B.AGTY_SYS_CD, B.BENEF_STAT_CD, 


sum(case When (select recip_ssn_nbr from dsnp.pr01_t_recip_sys where right(B.voucher_id_cd,1) = 'D' group by A.recip_ssn_nbr 
having min(A.anty_pymt_dt) = '2010-05-01' )  then 1 end)   AS "DISABILITY RETIREES"


FROM     DSNP.PR01_T_ANTY_PYMT A, 
         DSNP.PR01_T_RECIP_SYS 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

Unfortunately, I get errors about using the reserve word THEN. I don't know if it is just a simple syntax problem or SQL can't run this type of query.

gavakie
Posting Yak Master

221 Posts

Posted - 2010-05-11 : 12:38:16

You need an else in there.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-11 : 12:51:20

Try this:

SELECT B.AGTY_SYS_CD, B.BENEF_STAT_CD,

sum(case When (select 1 from
dsnp.pr01_t_recip_sys where
right(B.voucher_id_cd,1) = 'D'
group by A.recip_ssn_nbr
having min(A.anty_pymt_dt) = '2010-05-01' ) =1 then 1 end)
AS "DISABILITY RETIREES"

FROM DSNP.PR01_T_ANTY_PYMT A,
DSNP.PR01_T_RECIP_SYS 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

I have just resolved the issue with CASE stmt and not gone thru the logic you are using.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

sqlnoob35
Starting Member

10 Posts

Posted - 2010-05-11 : 13:42:29

Hey Bohra,

I get this message when I try your query - The operand of the column function "" includes a column function, a scalar fullselect, or a subquery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources