Case statement with multiple whens...not working!

0
votes

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
8
sqlsql-servercase
Which error do you get after removing the comma? - Heinzi
END AS MONTH gives me: Incorrect syntax near ','. After removing the comma I get: The multi-part identifier "day" could not be bound. - Daniel
@Daniel: This error has nothing to do with your CASE statement -- there is something wrong with your "day" clause. Show us the complete SQL statment, and we can work on fixing that. - Heinzi
The multi-part identifier "day" could not be bound. you need to tell the query which table "day" comes from. put the proper table name or alias in front of it: a.Day, a.year,... - KM.
@Heinzi thanks, here you go: insert into R2 select a.day, a.year, a.time, case when a.char046 is NULL then 'ERR' when a.char046 > '010' then '11+' else a.char046 end as B2, a.minute from R1 This gives me the bound error. With the comma after 'ERR' i get Incorrect syntax near ','. - Daniel

8 Answers

2
votes

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 ...
2
votes

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.

0
votes
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
0
votes

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]
0
votes

Put square brackets around Day and Year, like this:

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

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

0
votes

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'
0
votes

I think you are missing 'AS' after the end of the case.