CASE Statement Problem - Will This Even Work?

dvdaddict32 · #1 (**permalink**) 05-25-10, 12:45

Hey guys,

I tried running a query today, and I'm getting the error:

A CASE expression in a VALUES clause, IN predicate, GROUP BY clause, or ORDER BY clause cannot include a quantified predicate, an IN predicate using a fullselect, or an EXISTS predicate.

I am trying to use a select statement inside a CASE statement. I have not found any examples online of this type of structure, and I don't know if it is even allowed. Here is my query:

Code:

SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD,
      
      
      sum(case When RIGHT (VOUCHER_ID_CD,1) = 'D' and NOT EXISTS ( SELECT * FROM DSNP.PR01_T_ANTY_PYMT B                            
                   WHERE                                                            
                    A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
                                              
                    B.ANTY_PYMT_DT < '2010-05-01' )  then 1 end)  AS "DISABILITY RETIREES" 
         
         
FROM DSNP.PR01_T_RECIP_SYS A,
     DSNP.PR01_T_ANTY_PYMT B
       
WHERE    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

Since I will be performing multiple counts which must be grouped by A.AGTY_SYS_CD, the CASE statement appears to be my best option. However, the NOT EXISTS part may not work.

If anyone can help or offer a different method to achieve the same output, that would be great. I'm totally stuck on this one.

Thanks,

David

ARWinner · #2 (**permalink**) 05-25-10, 13:17

Try this:

Code:

with t1 (AGTY_SYS_CD,BENEF_STAT_CD,DISABILITY) as

(SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD,
      case When RIGHT (VOUCHER_ID_CD,1) = 'D' and NOT EXISTS ( SELECT * FROM DSNP.PR01_T_ANTY_PYMT B                            
                   WHERE                                                            
                    A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
                                              
                    B.ANTY_PYMT_DT < '2010-05-01' )  then 1 else 0 end
         
         
FROM DSNP.PR01_T_RECIP_SYS A      
WHERE    A.BENEF_STAT_CD IN ('AC', 'DP') 
AND      A.RECIP_TYPE_CD = '10' AND      A.BENEF_SEQ_NBR = 1 )
) select AGTY_SYS_CD,BENEF_STAT_CD,sum(DISABILITY) as disability_retirees
from t1 
GROUP BY AGTY_SYS_CD,BENEF_STAT_CD 
ORDER BY 3 DESC

dvdaddict32 · #3 (**permalink**) 05-25-10, 13:36

Quote:

Originally Posted by ARWinner

Try this:

Code:

with t1 (AGTY_SYS_CD,BENEF_STAT_CD,DISABILITY) as

(SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD,
      case When RIGHT (VOUCHER_ID_CD,1) = 'D' and NOT EXISTS ( SELECT * FROM DSNP.PR01_T_ANTY_PYMT B                            
                   WHERE                                                            
                    A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
                                              
                    B.ANTY_PYMT_DT < '2010-05-01' )  then 1 else 0 end
         
         
FROM DSNP.PR01_T_RECIP_SYS A      
WHERE    A.BENEF_STAT_CD IN ('AC', 'DP') 
AND      A.RECIP_TYPE_CD = '10' AND      A.BENEF_SEQ_NBR = 1 )
) select AGTY_SYS_CD,BENEF_STAT_CD,sum(DISABILITY) as disability_retirees
from t1 
GROUP BY AGTY_SYS_CD,BENEF_STAT_CD 
ORDER BY 3 DESC

I gave it a shot, but I come up with the same error. It sounds like DB2 just doesn't like SELECT statements inside of CASE statements. Thanks for suggestion though!

ARWinner · #4 (**permalink**) 05-25-10, 13:57

OK, Try this:

Code:

with t1 (AGTY_SYS_CD,BENEF_STAT_CD,D,E) as

(SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD,
      RIGHT (VOUCHER_ID_CD,1),
      (SELECT 1 FROM DSNP.PR01_T_ANTY_PYMT B                            
         WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
         B.ANTY_PYMT_DT < '2010-05-01'  fetch first 1 row only) 
         
         
FROM DSNP.PR01_T_RECIP_SYS A      
WHERE    A.BENEF_STAT_CD IN ('AC', 'DP') 
AND      A.RECIP_TYPE_CD = '10' AND      A.BENEF_SEQ_NBR = 1 )
) select AGTY_SYS_CD,BENEF_STAT_CD,count(*) as disability_retirees
from t1 
where d = 'D' and E is NULL 
GROUP BY AGTY_SYS_CD,BENEF_STAT_CD 
ORDER BY 3 DESC

Andy

Lenny77 · #5 (**permalink**) 05-25-10, 16:58

You can try also:

with t1 (AGTY_SYS_CD,BENEF_STAT_CD,DISABILITY) as
(SELECT A.AGTY_SYS_CD, A.BENEF_STAT_CD,
case When RIGHT (VOUCHER_ID_CD, 1) = 'D'
and 1 not in ( SELECT 1
FROM DSNP.PR01_T_ANTY_PYMT B
WHERE
A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND
B.ANTY_PYMT_DT < '2010-05-01' )
then 1
else 0
end
FROM DSNP.PR01_T_RECIP_SYS A
WHERE A.BENEF_STAT_CD IN ('AC', 'DP')
AND A.RECIP_TYPE_CD = '10'
AND A.BENEF_SEQ_NBR = 1
) select AGTY_SYS_CD,BENEF_STAT_CD,sum(DISABILITY) as disability_retirees
from t1
GROUP BY AGTY_SYS_CD,BENEF_STAT_CD
ORDER BY 3 DESC

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech