Select Case Where Like?

[enfuego]

Hey kids, got a problem. Here's the code:

Rev.techCompany CASE WHEN techCompany LIKE 'North_IR' THEN 'NorthIR'
           WHEN techCompany LIKE 'South_IR' THEN 'SouthIR'
           WHEN techCompany LIKE 'East_IR'  THEN 'EastIR'
           WHEN techCompany LIKE 'North_[^IR]' THEN 'NorthContractor'
           WHEN techCompany LIKE 'South_[^IR]' THEN 'SouthContractor'
           WHEN techCompany LIKE 'East_[^IR]' THEN 'EastContractor' END AS Region  From xxx.reviews AS Rev 
GROUP BY Region

What I am trying to accomplish w/ the above code is separate each techCompany into columns of region. The techCompany value will have region as part of the value. (i.e. North Company Name IR, or North Company Name). I am getting an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CASE'
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'AS'

Thank you for any advice.

[ck9663]

Hey kids, got a problem. Here's the code:

Rev.techCompany CASE WHEN techCompany LIKE 'North_IR' THEN 'NorthIR'
           WHEN techCompany LIKE 'South_IR' THEN 'SouthIR'
           WHEN techCompany LIKE 'East_IR'  THEN 'EastIR'
           WHEN techCompany LIKE 'North_[^IR]' THEN 'NorthContractor'
           WHEN techCompany LIKE 'South_[^IR]' THEN 'SouthContractor'
           WHEN techCompany LIKE 'East_[^IR]' THEN 'EastContractor' END AS Region  From xxx.reviews AS Rev 
GROUP BY Region

What I am trying to accomplish w/ the above code is separate each techCompany into columns of region. The techCompany value will have region as part of the value. (i.e. North Company Name IR, or North Company Name). I am getting an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CASE'
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'AS'

Thank you for any advice.

have you tried:

1. i hope there's a SELECT keyword in the beginning of your query.
2. put a comma after "Rev.techCompan y"
3. you have 2 fields on your SELECT fieldlist, but only 1 in GROUP BY. as a general rule, all non-aggregate function on your SELECT list must be included in your GROUP BY.
4.i don't (read: am not sure) think sql-server allow a GROUP BY on a generated field. you might need to duplicate the entire CASE...WHEN.. on your GROUP BY...

happy coding

-- CK

[enfuego]

have you tried:

1. i hope there's a SELECT keyword in the beginning of your query.
2. put a comma after "Rev.techCompan y"
3. you have 2 fields on your SELECT fieldlist, but only 1 in GROUP BY. as a general rule, all non-aggregate function on your SELECT list must be included in your GROUP BY.
4.i don't (read: am not sure) think sql-server allow a GROUP BY on a generated field. you might need to duplicate the entire CASE...WHEN.. on your GROUP BY...

happy coding

-- CK

Thank you for the reply. It seems to be working so far. I have to build some fill DB w/ dummy data to fully test but I stopped getting error codes. Thank you again.