“案例"“WHERE"中的语句SQL Server 2008 中的子句

2021-12-02 00:00:00 sql sql-server case

我正在处理一个在WHERE"子句中包含CASE"语句的查询.但是 SQL Server 2008 在执行时出现了一些错误.任何人都可以帮助我进行正确的查询吗?这是查询:

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

推荐答案

首先，CASE 语句必须是表达式的一部分，而不是表达式本身.

First off, the CASE statement must be part of the expression, not the expression itself.

换句话说，你可以:

WHERE co.DTEntered = CASE 
                          WHEN LEN('blah') = 0 
                               THEN co.DTEntered 
                          ELSE '2011-01-01' 
                     END

但它不会像你写的那样工作，例如:

But it won't work the way you have written them eg:

WHERE 
    CASE LEN('TestPerson')
        WHEN 0 THEN co.personentered  = co.personentered
   ELSE co.personentered LIKE '%TestPerson'
    END

使用这样的组合 OR 语句可能会更好:

You may have better luck using combined OR statements like this:

WHERE (
        (LEN('TestPerson') = 0 
             AND co.personentered = co.personentered
        ) 
        OR 
        (LEN('TestPerson') <> 0 
             AND co.personentered LIKE '%TestPerson')
      )

虽然，无论哪种方式，我都不确定您会获得多棒的查询计划.WHERE 子句中的这些类型的恶作剧通常会阻止查询优化器使用索引.

Although, either way I'm not sure how great of a query plan you'll get. These types of shenanigans in a WHERE clause will often prevent the query optimizer from utilizing indexes.

相关文章