I am working with a query which contains “CASE” statement within “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:
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