CASE Statement - An expression services limit has been reached

I'm getting the following error:

An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

I'm attempting to run the below query, however it appears there is one line too many in my case statement (when i remove the "London" Line, it works perfectly) or "Scotland" for example.

I can't think of the best way to split this statement. If i split it into 2 queries and union all, it does work. however the ELSE 'No Region' becomes a problem. Everything which is included in the first part of the query shows as "No Region" for the second part of the query, and vice versa.

(My end goal is essentially to create a list of customers per region) I can then use this as the foundation of a regional sales report.

Many Thanks

Andy

SELECT T0.CardCode, T0.CardName, T0.PostCode,
  
  CASE
WHEN T0.PostCodeABR IN  ('DG','KW','IV','PH','AB','DD','PA','FK','KY','G','EH','ML','KA','TD') THEN 'Scotland' 
WHEN T0.PostCodeABR IN  ('BT') THEN 'Ireland' 
WHEN T0.PostCodeABR IN  ('CA','NE','DH','SR','TS','DL','LA','BD','HG','YO','HX','LS','FY','PR','BB','L','WN','BL','OL') THEN 'North M62' 
WHEN T0.PostCodeABR IN  ('CH','WA','CW','SK','M','HD','WF','DN','HU','DE','NG','LN','S') THEN 'South M62' 
WHEN T0.PostCodeABR IN  ('LL','SY','LD','SA','CF','NP') THEN 'Wales' 
WHEN T0.PostCodeABR IN  ('NR','IP','CB') THEN 'East Anglia' 
WHEN T0.PostCodeABR IN  ('SN','BS','BA','SP','BH','DT','TA','EX','TQ','PL','TR') THEN 'South West' 
WHEN T0.PostCodeABR IN  ('LU','AL','HP','SG','SL','RG','SO','GU','PO','BN','RH','TN','ME','CT','SS','CM','CO') THEN 'South East' 
WHEN T0.PostCodeABR IN  ('ST','TF','WV','WS','DY','B','WR','HR','GL','OX','CV','NN','MK','PE','LE') THEN 'Midlands' 
WHEN T0.PostCodeABR IN  ('WD','EN','HA','N','NW','UB','W','WC','EC','E','IG','RM','DA','BR','CR','SM','KT','TW','SW') THEN 'London' 

ELSE 'No Region'

END AS 'Region'
   FROM [dbo].[REPS-PostcodeABBR] T0

Solution

As I mentioned in the comment, I would suggest you create a "lookup" table for the post codes, then all you need to do is JOIN to the table, and not have a "messy" and large CASE expression (T-SQL doesn't support Case (Switch) statements).

So your lookup table would look a little like this:

CREATE TABLE dbo.PostcodeRegion (Postcode varchar(2),
                                 Region varchar(20));
GO
--Sample data
INSERT INTO dbo.PostcodeRegion (Postcode,Region)
VALUES('DG','Scotland'),
      ('BT','Ireland'),
      ('LL','Wales');

And then your query would just do a LEFT JOIN:

SELECT RPA.CardCode,
       RPA.CardName,
       RPA.PostCode,
       COALESCE(PR.Region,'No Region') AS Region
FROM [dbo].[REPS-PostcodeABBR] RPA --T0 is a poor choice of an alias, there is no T not 0 in "REPS-PostcodeABBR"
     LEFT JOIN dbo.PostcodeRegion PR ON RPA.PostCodeABR = PR.Region;

Note you would likely want to INDEX the table as well, and/or apply a UNIQUE CONSTRAINT or PRIMARY KEY to the PostCode column.