case statement in sybase

Question

I have following requirement for Sybase Query:-

Exclude below if:

• The SSN has more or less than nine digits
• The SSN includes non-numeric characters
• The SSN is blank
• The SSN includes the same digits (for example, 000000000, 111111111 or 999999999)
• The SSN has a 9 as the first digit and a 7, 8 or 9 as the fourth digit
• The fourth and the fifth digits are 00 (except 800-00-0000)
• The sixth through ninth digits are 0000

I wrote case statement as below but it's not working, can someone help please:

SELECT CASE
    WHEN LEN(a.MEMBER_SSN) > 9 THEN ' '
    WHEN LEN(a.MEMBER_SSN) < 9 THEN ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) like '%[A-Z,a-z]%' then ' '
    WHEN LTRIM(RTRIM(WHEN a.MEMBER_SSN)) like '%[0-9]%' then ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) in ( '000000000','000000001','000000002','000000003','000000004','000000005','999999999','111111111','000000070','123456789','999999998','000000071','888888888', ) THEN ''
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '9__[789]%' THEN a.MEMBER_SSN ELSE ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '____[00]%' THEN a.MEMBER_SSN ELSE ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '_____[0000]%' THEN a.MEMBER_SSN ELSE ' '
    END AS SUBSCRIBER_SSN From dbo.MEMBER_TABLE a

Answer 1

The first problem is a syntax error. There can only be one ELSE per CASE:

CASE
  WHEN ... THEN ...
  WHEN ... THEN ...
  WHEN ... THEN ... ELSE ... -- this ELSE doesn't belong here
  WHEN ... THEN ... ELSE ... -- and this ELSE should go to the next line for readability
END      

Then, you want to select the SSN when none of the rejection conditions is met (NOT ... AND NOT ...), but

WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '9__[789]%' THEN a.MEMBER_SSN

would select the SSN regardless of the following conditions.

Here is one option how to write the conditions using AND and OR:

WHEN TRIM(a.member_ssn) = '800000000'
OR
(
  TRIM(a.member_ssn) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  AND
  TRIM(a.member_ssn) NOT LIKE '___00%'
  AND
  TRIM(a.member_ssn) NOT LIKE '%0000'
  AND
  TRIM(a.member_ssn) NOT LIKE '9__[789]%'
  AND
  TRIM(a.member_ssn) NOT IN ('000000000', '111111111', ... '999999999')
) THEN TRIM(a.member_ssn)
  ELSE ' '
END AS subscriber_ssn

Answer 2

Here is a shorter version:

SELECT (CASE WHEN LEN(a.MEMBER_SSN) <> 9 THEN ' '
             WHEN a.MEMBER_SSN LIKE '%[^0-9]%' THEN ' '
             WHEN REPLACE(a.MEMBER_SSN, LEFT(a.MEMBER_SSN, 1), '') = '' THEN ' '
             WHEN a.MEMBER_SSN LIKE '9__[789]%' THEN ' '
             WHEN a.MEMBER_SSN LIKE '___00%' AND a.MEMBER_SSN <> '800-00-0000' THEN ' '
             WHEN a.MEMBER_SSN LIKE '%0000' THEN ' '
             ELSE a.MEMBER_SSN
        END) AS SUBSCRIBER_SSN
From dbo.MEMBER_TABLE a

This is almost a direct transcription of your conditions.