Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

I am working with a query that contains the "CASE" statement within the "WHERE" clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:


    tl.storenum 'Store #', 

    co.ccnum 'FuelFirst Card #', 

    co.dtentered 'Date Entered',

    CASE st.reasonid 

        WHEN 1 THEN 'Active' 

   WHEN 2 THEN 'Not Active' 

   WHEN 0 THEN st.ccstatustypename 

   ELSE 'Unknown' 

    END 'Status',

    CASE st.ccstatustypename 

        WHEN 'Active' THEN ' ' 

   WHEN 'Not Active' THEN ' ' 

   ELSE st.ccstatustypename 

    END 'Reason',

    UPPER(REPLACE(REPLACE(co.personentered,'RT\\\\',''),'RACETRAC\\\\','')) 'Person Entered',

    co.comments 'Comments or Notes'


    comments co

    INNER JOIN cards cc ON co.ccnum=cc.ccnum

    INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid

    INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid

    INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd

    INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid

    LEFT JOIN stores s ON s.StoreNum = tl.StoreNum


    CASE LEN('TestPerson')

        WHEN 0 THEN co.personentered  = co.personentered

   ELSE co.personentered LIKE '%TestPerson'


    AND cc.ccnum = CASE LEN('TestFFNum')

        WHEN 0 THEN cc.ccnum 

   ELSE 'TestFFNum' 


    AND CASE LEN('2011-01-09 11:56:29.327') 

        WHEN 0 THEN co.DTEntered = co.DTEntered 


       CASE LEN('2012-01-09 11:56:29.327') 

           WHEN 0 THEN co.DTEntered >= '2011-01-09 11:56:29.327' 

      ELSE co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327' 



    AND tl.storenum < 699 

ORDER BY tl.StoreNum

1 Answer

0 votes
by (40.7k points)

You can use this:

WHERE co.DTEntered = CASE 

                          WHEN LEN('blah') = 0 

                               THEN co.DTEntered 

                          ELSE '2011-01-01' 


But it will not work the way you have mentioned them:



    CASE LEN('TestPerson')

        WHEN 0 THEN co.personentered  = co.personentered

   ELSE co.personentered LIKE '%TestPerson'


You can try using combined OR statements like this:


        (LEN('TestPerson') = 0 

             AND co.personentered = co.personentered



        (LEN('TestPerson') <> 0 

             AND co.personentered LIKE '%TestPerson')


These types of shenanigans in a WHERE clause will often prevent the query optimizer from utilizing indexes.

Note: The CASE statement must be part of the expression, not the expression itself.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL online course.

Related questions

+2 votes
1 answer
0 votes
1 answer
asked Jul 11, 2019 in SQL by Tech4ever (20.3k points)

Browse Categories