Case statement with multiple whens…not working?

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.

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.

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

Without commas gives me this message immediately for every day, year, etc. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "day" could not be bound. – Daniel Feb 2 '10 at 14:59 Commas are still required to separate the columns of the returned recordset. You just don't need them within the case statement.

– Mayo Feb 2 '10 at 15:01 right, I meant without that one comma after 'ERR'. Same error – Daniel Feb 2 '10 at 15:02 Ah, then that's another error. I would suggest googling the error - i.e.

Aartemiou.blogspot. Com/2009/01/…. – Mayo Feb 2 '10 at 15:04 The error you get suggests that the field "day" is not part of the table you're selecting from.

Check the schema of the table you're selecting from. – Ender Feb 2 '10 at 15:11.

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.

Still getting errors. See above. – Daniel Feb 2 '10 at 15:03.

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.

Put square brackets around Day and Year, like this: select a. Day, a. Year, ...

It's just an example, day isn't in the actual statement – Daniel Feb 2 '10 at 15:04 Please post the actual statement. – RedFilter Feb 2 '10 at 15:05.

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

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 be 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 be on a. RECORD_ID = b. RECORD_ID where a.

POL_OGN_EFF_DT >= '2008-11-01.

Select a. POL_PRI_RSK_ST_CD as State, a. MASTER_COMPANY_NBR as Original_CO, 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 be 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 – Daniel Feb 2 '10 at 15:23 I edited the question. Thanks – Daniel Feb 2 '10 at 15:26.

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

As is optional, not required... – Charles Bretana Feb 2 '10 at 15:16.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.