SQL CASE Statement Within WHERE Clause

Question

I'm trying to do a WHERE clause that contains a CASE statement. If my field equals a certain text value then I want to apply one set of logic, if it contains a separate value then I want it to filter a different way.

I have this code but I cannot make it work:

    AND CASE DO11_DOCUM_MG36 WHEN 'TSC-ORDINE-RIC'
                                               THEN 
                                               (CONVERT(DATE,ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS,DATEADD(day,ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4,15),dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)),102) <= '2016-02-29' AND dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF is not null) AND (dbo.DO36_DOCCORPOEST.DO36_DATAST1 is null or CONVERT(DATE,dbo.DO36_DOCCORPOEST.DO36_DATAST1,102) >= '2016-02-01')
                                               ELSE
                                               (CONVERT(DATE,ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS,DATEADD(day,ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4,15),dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)),102) BETWEEN '2016-02-01' AND '2016-02-29' AND dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF is not null) 
                                               END

Thanks

Possible duplicate of [Conditional where clause in Sql Server?](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, Jun 03 '16 at 13:11

Rich Benner · Accepted Answer · 2016-06-03T12:36:53.070

I've formatted this differently to make it easier to read. The logic for a CASE statement should be CASE WHEN ... THEN ... ELSE... END. You can put AND clauses in the WHEN statement but I'm not aware that you can use the AND in your THEN clause like you are.

Your WHEN statement doesn't produce a boolian value, there's no 'WHEN x = y THEN' there's just a 'WHEN x THEN'.

You should also have nothing between CASE and WHEN at the beginning of your statement, this is never going to work.

Please also start using table aliases, it will make your code much easier to read.

AND CASE DO11_DOCUM_MG36
        WHEN 'TSC-ORDINE-RIC'
            THEN (
                    CONVERT(DATE, ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS, DATEADD(day, ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4, 15), dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)), 102) <= '2016-02-29'
                    AND dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF IS NOT NULL
                    )
                AND (
                    dbo.DO36_DOCCORPOEST.DO36_DATAST1 IS NULL
                    OR CONVERT(DATE, dbo.DO36_DOCCORPOEST.DO36_DATAST1, 102) >= '2016-02-01'
                    )
        ELSE (
                CONVERT(DATE, ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS, DATEADD(day, ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4, 15), dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)), 102) BETWEEN '2016-02-01'
                    AND '2016-02-29'
                AND dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF IS NOT NULL
                )
        END

CASE statement logic should read something like this;

CASE
    WHEN a.Field1 = 'SomeText'
        THEN CONVERT(DATE, a.DateTimeField)
    WHEN a.Field1 = 'DifferentText' AND a.Field2 = 'YetMoreText'
        THEN CONVERT(DATE, a.DifferentDateTimeField)
    ELSE 'DoSomethingElse'
END

Some further reading;

http://www.techonthenet.com/sql_server/functions/case.php

Reading your logic, I'm not convinced you need a case statement here at all, something like this would do you;

AND
    (
        CONVERT(DATE, ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS, DATEADD(day, ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4, 15), dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)), 102) <= '2016-02-29'
        AND dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF IS NOT NULL
    )
AND
    (         
        (
        dbo.DO36_DOCCORPOEST.DO36_DATAST1 IS NULL
        OR CONVERT(DATE, dbo.DO36_DOCCORPOEST.DO36_DATAST1, 102) >= '2016-02-01'
        )
        OR          
        CONVERT(DATE, ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS, DATEADD(day, ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4, 15), dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)), 102) BETWEEN '2016-02-01'AND '2016-02-29'
    )

As per your comment, you could use this;

AND (
        (   OrderType = 'TSC-ORDINI-RIC'
            AND
            'WHERE CONDITION 1 GOES HERE'
        )
        OR 
        (   OrderType = 'TSC-ORDINI'
            AND
            'WHERE CONDITION 2 GOES HERE'
        )
    )

thanks Rich but even if i remove all the AND inside the THEN leaving only CONVERT(DATE, ISNULL(dbo.DO31_DOCCORPOORD.DO31_DATACONS, DATEADD(day, ISNULL(dbo.DO17_DOCTESTAPERS.DO17_NUMPERS4, 15), dbo.DO12_DOCTESTARIF.DO12_DATAVSCONF)), 102) <= '2016-02-29' still output the sintax error commented before. — user3348353, Jun 03 '16 at 10:28
What about everything else that's wrong with the case statement? I'm not sure you even need a case statement in this instance, I'm just going to update my answer without a CASE — Rich Benner, Jun 03 '16 at 10:33
what i am trying to do is: when the order type is TSC-ORDINI-RIC i need to use a where condition when the order is TSC-ORDINI i need to use another where condition — user3348353, Jun 03 '16 at 10:53
Glad it worked for you, feel free to upvote the answer if it has met your requirements. — Rich Benner, Jun 03 '16 at 12:38

SQL CASE Statement Within WHERE Clause

1 Answers1