Using CASE WHEN to return a logical value in WHERE clause

Question

Using CASE WHEN to return a logical value in WHERE clause

Iulian -207023

SSCertifiable

Points: 7639
More actions
September 1, 2010 at 3:18 am

#227064

Hello,
Using AdventureWorks I have to present the customers
having the option to select all in a given group of accounts or to select for an individual account.
I wrote the query bellow but I get error: Incorrect syntax near the keyword 'LIKE'.
What do you think is the cause and the solution for this?
Thanks a lot,
Iulian
P.S. any opinion is highly appreciated
USE AdventureWorks
DECLARE @FilterOption AS VARCHAR(10)
SET@FilterOption = 'by group'
--SET@FilterOption = 'individual'
DECLARE @AccountFilter AS VARCHAR(9)
SET@AccountFilter = '000001'
--SET@AccountFilter = 'AW00000321'
SELECT
AccountNumber,
CustomerID,
TerritoryID,
CustomerType,
ModifiedDate
FROM Customer AS c
WHERE
(
CASE
WHEN ( @FilterOption = 'by group' ) THEN ( c.AccountNumber LIKE ( 'AW' + @AccountFilter + '%' ) )
WHEN ( @FilterOption = 'individual' ) THEN ( c.AccountNumber = @AccountFilter )
ELSE 0
END
)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

A conditional expression can't appear in your THEN clause. The generic way to fix this is to move it to the WHERE clause with an AND.

WHERE CASE

WHEN ( @FilterOption = 'by group' )

AND ( c.AccountNumber LIKE ( 'AW' + @AccountFilter + '%' ) )

THEN 1

WHEN ( @FilterOption = 'individual' )

AND ( c.AccountNumber = @AccountFilter )

THEN 1

ELSE 0

END = 1

Although in specific cases, there may be other ways to rewrite it. For instance, you could use the fact that LIKE without wildcards or brackets is equivalent to "=".

WHERE c.AccountNumber LIKE CASE @FilterOption

WHEN 'by group' THEN 'AW' + @AccountFilter + '%'

ELSE @AccountFilter

END

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

CASE is used with values, you are attempting to use CASE to build part of a statement. You could go the whole hog and perform the latter successfully using dynamic SQL like this:

DECLARE @StringToExecute VARCHAR(MAX), @FilterOption VARCHAR(20), @AccountFilter VARCHAR(20)

SET @FilterOption = 'individual'

SET @AccountFilter = '000012'

SET @StringToExecute =

'SELECT

AccountNumber,

CustomerID,

TerritoryID,

CustomerType,

ModifiedDate

FROM Customer AS c

WHERE ' +

CASE @FilterOption

WHEN 'by group' THEN 'c.AccountNumber LIKE ''AW' + @AccountFilter + '%'''

WHEN 'individual' THEN 'c.AccountNumber = ''' + @AccountFilter + ''''

ELSE '1 = 1'

END

PRINT @StringToExecute

As Drew has shown there are alternatives which may or may not be better for this case. Check out this recent article for some handy hints on conditional WHERE processing:

http://www.sqlservercentral.com/articles/conditional/70889/[/url]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Very useful and inspiring.

Thank you very much Drew and Chris.

Have a nice day,

Iulian