CASE statement within WHERE clause in SQL Server 2008

0 votes

The query I'm working with has a "CASE" statement in the "WHERE" clause. However, while running it, SQL Server 2008 is throwing several issues. Can someone please guide me to the right question? This is the problem:

SELECT
    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'
FROM 
    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
WHERE 
    CASE LEN('TestPerson')
        WHEN 0 THEN co.personentered  = co.personentered
   ELSE co.personentered LIKE '%TestPerson'
    END 
    AND cc.ccnum = CASE LEN('TestFFNum')
        WHEN 0 THEN cc.ccnum 
   ELSE 'TestFFNum' 
    END 
    AND CASE LEN('2011-01-09 11:56:29.327') 
        WHEN 0 THEN co.DTEntered = co.DTEntered 
   ELSE 
       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' 
       END 
    END
    AND tl.storenum < 699 
ORDER BY tl.StoreNum

Can someone please help- me with this?

Sep 3 in Database by Kithuzzz
• 11,640 points
7 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers

SQL use CASE statement in WHERE IN clause

Can you use a case statement in ...READ MORE

Aug 9 in Database by Kithuzzz
• 11,640 points
33 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

Without a case statement: SELECT column1, ...READ MORE

answered Feb 7 in Database by Vaani
• 7,020 points
115 views
0 votes
1 answer

SQL Server CASE .. WHEN .. IN statement

Two forms of CASE statements are getting ...READ MORE

answered Feb 8 in Database by Vaani
• 7,020 points
74 views
0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19 in Database by Kithuzzz
• 11,640 points
26 views
0 votes
0 answers

I want to use CASE statement to update some records in sql server 2005

UPDATE dbo.TestStudents SET LASTNAME = ( ...READ MORE

Sep 2 in Database by Kithuzzz
• 11,640 points
32 views
0 votes
1 answer

Best way to do nested case statement logic in SQL Server

Try some sort of COALESCE trick: SELECT COALESCE( ...READ MORE

answered Sep 19 in Database by narikkadan
• 19,680 points
10 views
0 votes
2 answers

Case in Select Statement

I think these could be helpful for ...READ MORE

answered Feb 4 in Database by Neha
• 8,920 points
78 views
0 votes
1 answer

How do I perform an IF THEN statement in an SQL SELECT?

The CASE statement is the closest to IF in ...READ MORE

answered Feb 16 in Database by Vaani
• 7,020 points
115 views
0 votes
1 answer

Case in Select Statement

 use: SELECT CASE ...READ MORE

answered Jun 6 in Ask us Anything! by nisha
• 2,180 points
28 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
330 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP