469,085 Members | 1,010 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,085 developers. It's quick & easy.

SELECT CASE WHEN IN() statement

92
Hi,

I have a query that is sorting people into their respective areas of the country and need to assign each postcode the correct label (Eg: 'North', 'Wales', etc.). I am trying to do this in a CASE statement at the moment but it does not like the IN() part, returning all the values with the 'N/A' label. Everything parses fine and the query runs, it just doesn't assign the correct labels.

Expand|Select|Wrap|Line Numbers
  1. SELECT    PH.SERIALNUMBER,
  2.     PH.PLEDGEID,
  3.     PH.INSTALMENTVALUE AS AVERAGE,
  4.  
  5.     CASE WHEN C.POSTCODE IN ('KW%','HS%','IV%','PH%','AB%','DD%','PA%','KA%','FK%','KY%','EH%','TD%','DG%','ML%','G%','ZE%') THEN 'SCOTLAND'
  6.     WHEN C.POSTCODE IN ('NE%','CA%','DH%','SR%','TS%','DL%','LA%','IM%','FY%','PR%','BB%','BD%','HG%','YO%','LS%','HX%','OL%','BL%','WN%','L%','CH%','CW%','WA%','M%','SK%','S%','HD%','WF%','DN%','HU%') THEN 'NORTH'
  7.     WHEN C.POSTCODE IN ('BT%') THEN 'NORTHERN IRELAND'
  8.     WHEN C.POSTCODE IN ('LL%','SY%','LD%','SA%','NP%','CF%') THEN 'WALES'
  9.     WHEN C.POSTCODE IN ('TF%','ST%','DE%','NG%','PE%','LE%','WS%','WV%','DY%','HR%','WR%','B%','CV%','NN%') THEN 'MIDLANDS'
  10.     WHEN C.POSTCODE IN ('TR%','PL%','TQ%','EX%','TA%','DT%','BA%','BS%','GL%','OX%','SN%','SP%','BH%') THEN 'SOUTH WEST'
  11.     WHEN C.POSTCODE IN ('NR%','IP%','CB%','CO%','SG%','MK%','LU%','HP%','AL%','WD%','EN%','CM%','RG%','SL%','GU%','SO%','PO%','BN%','RH%','TN%','ME%','CT%','SS%','GY%','JE%') THEN 'SOUTH EAST'
  12.     WHEN C.POSTCODE IN ('HA%','UB%','TW%','KT%','NW%','W%','SW%','SM%','CR%','N%','WC%','EC%','SE%','BR%','DA%','E%','IG%','RM%') THEN 'LONDON'
  13.     ELSE 'N/A'
  14.     END AS REGION,
  15.  
  16.     CASE WHEN PH.SERIALNUMBER IN (SELECT DISTINCT(BD1.SERIALNUMBER) FROM BATCHDETAIL BD1 WHERE ((BD1.DESTINATIONCODE) IN('sp%','ch%','sum%','may%','AUT%') AND ((BD1.DATEOFPAYMENT) <= GETDATE() AND (BD1.DATEOFPAYMENT) >= (dateadd(year,-1,getdate()))))) THEN 1 ELSE 0 END AS APPEALS,
  17.  
  18.     C.EMAILADDRESS AS EMAILS,
  19.     C.DATEOFBIRTH AS DOBS
  20.  
  21.     FROM (PLEDGEHEADER PH INNER JOIN BATCHDETAIL BD ON PH.PLEDGEID = BD.PLEDGEID) INNER JOIN CONTACT C ON PH.SERIALNUMBER = C.SERIALNUMBER
  22.     WHERE (((PH.PAYMENTFREQUENCY)='monthly') AND ((PH.PLEDGESTATUS)='active') AND ((BD.DESTINATIONCODE) IN('_ptr','_sto','pe%','tc01','pcy08','mjp','wap','ps059','scp','ps%','sp%','ch%','sum%','may%','AUT%')))
When I change the WHEN to
Expand|Select|Wrap|Line Numbers
  1. WHEN C.POSTCODE LIKE 'HA%' OR C.POSTCODE LIKE 'UB%' OR C.POSTCODE LIKE 'TW%' etc
the labels are properly assigned but the whole statement is significantly longer and, not going into the details of why, doesn't fit into the SQL editior in the report manager that is used here.

It is a ridiculous limit on the queries that can be made, but it is the way it is, and this one would fit if the IN() statement worked.

Any help is much appreciated,

NDayave
Mar 29 '10 #1
4 21543
IN Statement "Determines whether a specified value matches any value in a sub query or a list". We can't use the IN with ('KW%'). If you are using % then you need to use LIKE operator only.
If you want to use the IN then give the full value inside the IN (‘Full Name stored in Table’), otherwise in your table you can add the column Region in existing table “CONTACT C” and bring the values directly from it.

Thanks,
JK
Mar 29 '10 #2
NDayave
92
Cheers for that, made it "LEFT(POSTCODE,2) IN ('KW', 'W1', 'W2', etc)" which also clears up some ambiguity in the selection process Eg: 'L%' and 'LA%'

Thanks again,

NDayave
Mar 30 '10 #3
Rikkip
2
You could use Substr(POSTCODE,1,4) IN ('W1', 'W2', etc)

This would give the first 4 characters of the postcode and be more accurate than 'W1%', 'W2%',
Oct 27 '10 #4
ck9663
2,878 Expert 2GB
If that's the case, put those postcode in a table and use EXISTS to make it faster...

Happy Coding!!!

~~ CK
Oct 27 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Tcs | last post: by
8 posts views Thread by | last post: by
2 posts views Thread by scole954387 | last post: by
1 post views Thread by microsoft.public.dotnet.languages.vb | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.