Using CASE WHEN to return a logical value in WHERE clause

  • Iulian -207023

    SSCertifiable

    Points: 7509

    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

    )

  • drew.allen

    SSC Guru

    Points: 76737

    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: 186107

    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]

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Very useful and inspiring.

    Thank you very much Drew and Chris.

    Have a nice day,

    Iulian

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

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