SELECT CASE WHEN IN() statement

[NDayave]

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.

SELECT    PH.SERIALNUMBER,
    PH.PLEDGEID,
    PH.INSTALMENTVALUE AS AVERAGE,
 
    CASE WHEN C.POSTCODE IN ('KW%','HS%','IV%','PH%','AB%','DD%','PA%','KA%','FK%','KY%','EH%','TD%','DG%','ML%','G%','ZE%') THEN 'SCOTLAND'
    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'
    WHEN C.POSTCODE IN ('BT%') THEN 'NORTHERN IRELAND'
    WHEN C.POSTCODE IN ('LL%','SY%','LD%','SA%','NP%','CF%') THEN 'WALES'
    WHEN C.POSTCODE IN ('TF%','ST%','DE%','NG%','PE%','LE%','WS%','WV%','DY%','HR%','WR%','B%','CV%','NN%') THEN 'MIDLANDS'
    WHEN C.POSTCODE IN ('TR%','PL%','TQ%','EX%','TA%','DT%','BA%','BS%','GL%','OX%','SN%','SP%','BH%') THEN 'SOUTH WEST'
    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'
    WHEN C.POSTCODE IN ('HA%','UB%','TW%','KT%','NW%','W%','SW%','SM%','CR%','N%','WC%','EC%','SE%','BR%','DA%','E%','IG%','RM%') THEN 'LONDON'
    ELSE 'N/A'
    END AS REGION,
 
    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,
 
    C.EMAILADDRESS AS EMAILS,
    C.DATEOFBIRTH AS DOBS
 
    FROM (PLEDGEHEADER PH INNER JOIN BATCHDETAIL BD ON PH.PLEDGEID = BD.PLEDGEID) INNER JOIN CONTACT C ON PH.SERIALNUMBER = C.SERIALNUMBER
    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

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

[jvskarthick]

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

[NDayave]

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

[Rikkip]

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%',

[ck9663]

If that's the case, put those postcode in a table and use EXISTS to make it faster...

Happy Coding!!!

~~ CK