How Do You Reference a Case Statement Alias in the Group By?

[lrdave36]

Hey guys,

SQL keeps complaining that A.STATUS is not valid in the context with the GROUP BY.


Am I doing something wrong here?

SELECT   distinct a.mbr_ssn_nbr,
          b.mbr_f_nm,
          b.mbr_l_nm,
         sum(c.mbr_svc_cr_yy_ct) as TOTAL_YEARS,
         sum(c.mbr_svc_cr_mm_ct) as TOTAL_MONTHS,
         a.mbr_stat_cd,
         CASE
         when a.mbr_stat_cd = 'A' then 'ACTIVE'
         when a.mbr_stat_cd = '2' then 'INACTIVE'
         
         end as STATUS,
         a.mbr_sys_cd,
         a.mbr_vest_ind
         
         


FROM     dsnp.pr01_t_mbr_sys a,
         dsnp.pr01_t_mbr     b,
         dsnp.pr01_t_mbr_hist c  
         
         WHERE    a.MBR_SSN_NBR=b.MBR_SSN_NBR 
AND      a.MBR_SSN_NBR=c.MBR_SSN_NBR 
AND      a.MBR_STAT_CD IN ('A') 
AND      b.MBR_RECIPR_SYS_IND = 'Y' 
AND      not exists (select c.recip_ssn_nbr from dsnp.pr01_t_recip_sys c                  where c.recip_ssn_nbr = a.mbr_ssn_nbr                  and c.benef_stat_cd = 'DI')
AND      hist_categ_cd = '10'



group by 

a.mbr_ssn_nbr,
b.mbr_f_nm,
b.mbr_l_nm,
a.STATUS,
a.mbr_stat_cd,
a.mbr_sys_cd,
a.mbr_vest_ind

 

[gmmastros]

try this:

group by

a.mbr_ssn_nbr,
b.mbr_f_nm,
b.mbr_l_nm,
CASE
         when a.mbr_stat_cd = 'A' then 'ACTIVE'
         when a.mbr_stat_cd = '2' then 'INACTIVE'
         
         end,
a.mbr_stat_cd,
a.mbr_sys_cd,
a.mbr_vest_ind

Note that I removed "A.Status" and added the contents of the case expression instead.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lrdave36]

Thanks George, that worked! I actually had tried inserting the entire CASE statement in the GROUP BY, but I had END AS STATUS as well.

 

[r937]

alternatively, go ahead and use the column alias for the CASE expression in the GROUP BY clause, but don't put the "a." prefix on it, because that's what was causing the original error

SELECT DISTINCT a.mbr_ssn_nbr
     , b.mbr_f_nm
     , b.mbr_l_nm
     , SUM(c.mbr_svc_cr_yy_ct) as TOTAL_YEARS
     , SUM(c.mbr_svc_cr_mm_ct) as TOTAL_MONTHS
     , a.mbr_stat_cd
     , CASE WHEN a.mbr_stat_cd = 'A' 
            THEN 'ACTIVE'    
            WHEN a.mbr_stat_cd = '2' 
            THEN 'INACTIVE'
        end as STATUS
     , a.mbr_sys_cd
     , a.mbr_vest_ind   
  FROM dsnp.pr01_t_mbr_sys a
     , dsnp.pr01_t_mbr     b
     , dsnp.pr01_t_mbr_hist c                    
 WHERE a.MBR_SSN_NBR = b.MBR_SSN_NBR 
   AND a.MBR_SSN_NBR = c.MBR_SSN_NBR 
   AND a.MBR_STAT_CD IN ('A') 
   AND b.MBR_RECIPR_SYS_IND = 'Y' 
   AND NOT EXISTS 
       ( SELECT c.recip_ssn_nbr 
           FROM dsnp.pr01_t_recip_sys c    
          WHERE c.recip_ssn_nbr = a.mbr_ssn_nbr   
            AND c.benef_stat_cd = 'DI' )
   AND hist_categ_cd = '10'
GROUP 
    BY a.mbr_ssn_nbr
     , b.mbr_f_nm
     , b.mbr_l_nm
     , [red]STATUS[/red]
     , a.mbr_stat_cd
     , a.mbr_sys_cd
     , a.mbr_vest_ind

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

This is not going to work. The only place in a query where you can use the new alias (in SQL Server) is the ORDER BY Clause.

You may want to read answers to this interesting quiz by Itzik Ben-Gan

http://beyondrelational.com/quiz/sq...olumn-alias-defined-in-the-select-clause.aspx

There are 3 possible solutions:
1. Repeat the expression in the GROUP BY
2. Create this expression using CROSS APPLY technique
3. Use derived table or CTE


PluralSight Learning Library

 

[r937]

sorry, i have not been keeping up i guess

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon