July 5, 2016 at 3:23 am
Hi all, I'm trying to integrate a Case statement into the following code:
Select Distinct [User name]
FROM MeADBA.dbo.DBUserDetails
Where
[User name] not like 'PERLON\%'
And [User name] not like 'RMH\%'
And [User name] not like 'RMH\Admin%'
The 'AdminFunkdoobiest' value should not be excluded from the result set.
So, here's what I did...
Select Distinct [User name]
FROM MeADBA.dbo.DBUserDetails
Where
Case when [User name] like 'RMH\AdminFunkdoobiest' then 'RMH\AdminFunkdoobiest'
Else
[User name] not like 'PERLON\%'
And [User name] not like 'RMH\%'
And [User name] not like 'RMH\Admin%'
Syntax errors everywhere!! Can you guys get the syntax right?
Thanks
July 5, 2016 at 3:31 am
No CASE expression needed - change your WHERE clause:
SELECT DISTINCT [User name]
FROM MeADBA.dbo.DBUserDetails
WHERE [User name] NOT LIKE 'PERLON\%'
AND ([User name] NOT LIKE 'RMH\%' OR [User name] = 'RMH\AdminFunkdoobiest')
John
July 5, 2016 at 3:49 am
To explain why you're getting syntax errors, the output of a case statement must be an expression (constant, parameter, column, variable or function), not a clause or a predicate of the query.
CASE WHERE SomeValue = 1 THEN Column1 + Column2 ELSE Column3 END --- valid
CASE WHERE SomeValue = 1 THEN Column1 = Column2 ELSE Column3 = 0 END --- not valid, returning predicates
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2016 at 4:41 am
Thanks Gail for the case syntax summary.
But in this question's context, its out of scope right?
July 8, 2016 at 8:39 am
A CASE expression is an expression, just like a formula or function.
Its logic produces a single scalar value.
It is not another version of an IF.. THEN... ELSE construct.
CASE cannot control which segments of a single query are included, as you are trying to do.
For that matter, neither can IF...THEN...ELSE.
Nothing can.
If you use dynamic SQL you can use CASE expressions to help build a string that could then be executed using sp_executeSQL, but you what you are trying to do above simply isn't how SQL works. Sorry.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply