IN operator in SELECT CASE statement

[naren6876]

Hi,

The database is DB2.

My requirement is

If the first 6 characters of RECORD_ID = LOC_ID then get the REGION_NAME from LOC_INFO table.

If you are not finding some of the records then fall back would be to compare the first 3 characters.

I wrote a query as below, but erroring out with the following error

Error: SQL0104N An unexpected token "REGION_NAME" was found following "FO) THEN Select". Expected tokens may include: "END". SQLSTATE=42601

(State:42601, Native Code: FFFFFF98)

Query is:
--------------
Select
CASE
WHEN substr(RECORD_ID,1,6) IN (Select LOC_ID From LOC_INFO)
THEN Select REGION_NAME From LOC_INFO;
WHEN substr(RECORD_ID,1,3) IN (Select LOC_ID From LOC_INFO)
THEN Select REGION_NAME From LOC_INFO;
ELSE ' '
END CASE
from RECORD_INFO



Any clue would be much appreciated.

Thanks in advance.

[ray.wurlod]

I don't think the semi-colons within the CASE construct are correct, but it's been some time since I wrote Oracle queries in anger.