CASE statement syntax problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE statement syntax problem

Author

Topic

coleman8er
Starting Member

7 Posts

Posted - 2008-01-16 : 13:26:18

I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.

Error I'm getting:
Incorrect syntax near the keyword 'AS'.

Here's where the error lies:
CASE
(SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID)
WHEN False THEN
'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber
ELSE
vwEmp.SocialSecurityNumber
END

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-16 : 13:30:13

try:

CASE
(SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID)
WHEN False THEN
'xxx-xx-xxxx' + right(SocialSecurityNumber,0)
ELSE
vwEmp.SocialSecurityNumber
END AS SocialSecurityNumber

I am a bit confused on what you are trying to do with the statement in blue.

Are you trying to display the last 4 of the social(xxx-xx-1234)?
'xxx-xx-' + right(SocialSecurityNumber,4)

coleman8er
Starting Member

7 Posts

Posted - 2008-01-16 : 13:37:42

that solved my problem with AS, now i am getting another error:

Invalid column name 'False'.

i tried using Users.DisplaySSN = false in place of false, gave me a 'problem near =' error

so close, i can taste it. thanks for your assistance.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-16 : 13:40:44

Im assuming DisplaySSN's datatype is char.

Wrap "false" with single quotes ('false') so that it reads it as a character string.

coleman8er
Starting Member

7 Posts

Posted - 2008-01-16 : 13:41:23

quote:

I am a bit confused on what you are trying to do with the statement in blue.

Are you trying to display the last 4 of the social(xxx-xx-1234)?
'xxx-xx-' + right(SocialSecurityNumber,4)

i was originally going to show the last 4 digits of the ssn, but i opted not to show it. sorry for the confusion.

coleman8er
Starting Member

7 Posts

Posted - 2008-01-16 : 13:44:14

quote:
Originally posted by jdaman

Im assuming DisplaySSN's datatype is char.

Wrap "false" with single quotes ('false') so that it reads it as a character string.

perfect! thank you for your help

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-16 : 13:44:48

quote:
Originally posted by coleman8er

i was originally going to show the last 4 digits of the ssn, but i opted not to show it. sorry for the confusion.

I would remove " + right(SocialSecurityNumber,0)" from the statement then.

--------------------------------------------------------------------
JD

"Research is what I'm doing when I don't know what I'm doing."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources