Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: CASE Statement Problem - Will This Even Work?

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by ARWinner View Post
    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!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Use NOT IN instead of NOT EXISTS

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •