Case statement with multiple whens...not working!

Question

This is a simplified version of what I'm doing, but I can't get anything to work. The statement gives me an error without the comma after 'ERR'. I want the column to be 'Month' and I tohught this would work but I'm having a ton of trouble. Thanks for your help!

   select 
 a.POL_PRI_RSK_ST_CD, a.MASTER_COMPANY_NBR,

case

when a.char046 is NULL then 'ERR'

when a.char046 > '010' then '11+'

else a.char046 end as Policy_Years,

a.Last7Days, a.Last30Days, a.Last90Days

from reporting a inner join

Repository b 

on a.RECORD_ID = b.RECORD_ID

where a.POL_OGN_EFF_DT >= '2008-11-01'

group by

a.POL_PRI_RSK_ST_CD, a.MASTER_COMPANY_NBR, 

case

when a.char046 is NULL then 'ERR'

when a.char046 > '010' then '11+'

else a.char046 end as Policy_Years,

a.Last7Days, a.Last30Days, a.Last90Days

Answer 1

Try it without commas... example to follow.

select  
   case 
      when a.month is NULL then 'ERR'
      when a.month > '011' then '12' 
      else a.month 
   end as Month, 
   a.Last7Days 
from ... 

Answer 2

select 
     day, year,
case
   when a.month is NULL then 'ERR'
   when a.month > '011' then '12'
   else a.month end as Month,
 a.Last7Days

Answer 3

Hi, i think you are missing 'AS' after the end of the case Best Regards, Iordan

Answer 4

Fix the commas :

select 
     day, year,
     case
       when a.month is NULL then 'ERR'
       when a.month > '011' then '12'
       else a.month 
     end Month,
     a.Last7Days
from [table]

Answer 5

Put square brackets around Day and Year, like this:

select a.[Day], a.[Year], ...

Answer 6

Addressing your new error msg about 'multi-part identifier 'day',

Are day and year columns in the table ? What comes after the From in your query ? Are you joining multiple tables together in this? Please show the entire query?

ok, based on yr edited question, (You can't use an alias in a Group By) try this:

select a.POL_PRI_RSK_ST_CD, 
  a.MASTER_COMPANY_NBR,
  case when a.char046 is NULL then 'ERR'
       when a.char046 > '010' then '11+'
       else a.char046 end as Policy_Years,
  a.Last7Days, a.Last30Days, a.Last90Days
from reporting a 
   join Repository b 
      on a.RECORD_ID = b.RECORD_ID
where a.POL_OGN_EFF_DT >= '2008-11-01'
group by a.POL_PRI_RSK_ST_CD, 
   a.MASTER_COMPANY_NBR, 
   case when a.char046 is NULL then 'ERR'
        when a.char046 > '010' then '11+'
        else a.char046 end,
   a.Last7Days, a.Last30Days, a.Last90Days

but actually, you have no aggregate functions in there at all, just a group by on every expression in the select, so all you need is a Distinct key word , you don;t need the group by at all:

select Distinct a.POL_PRI_RSK_ST_CD, 
  a.MASTER_COMPANY_NBR,
  case when a.char046 is NULL then 'ERR'
       when a.char046 > '010' then '11+'
       else a.char046 end as Policy_Years,
  a.Last7Days, a.Last30Days, a.Last90Days
from reporting a 
   join Repository b 
      on a.RECORD_ID = b.RECORD_ID
where a.POL_OGN_EFF_DT >= '2008-11-01'

Answer 7

You cannot use an AS alias in the GROUP BY. The expression should match the expression in your SELECT without the alias.

Answer 8

Note: This is the outcome of the debugging session in the question comments.

The error Incorrect syntax near the keyword 'as'. was caused by as Policy_Years in the GROUP BY clause. You are not allowed to use as within a GROUP BY clause.