CASE statement within WHERE clause in SQL Server 2008

Question

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  gt    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  lt  699  ORDER BY tl StoreNum

User · Answer

here is my solution  AND CLI PE NOM Like       ISNULL  NomClient  CLI PE NOM          Regads  Davy

User · Answer

First off  the CASE statement must be part of the expression  not the expression itself    In other words  you can have   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    You may have better luck using combined OR statements like this   WHERE            LEN  TestPerson     0               AND co personentered   co personentered                    OR           LEN  TestPerson    lt  gt  0               AND co personentered LIKE   TestPerson             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

User · Answer

Try the following   select   From emp master  where emp last name   case emp first name   when  test     then  test    when  Mr name  then  name  end

User · Answer

This should solve your problem for the time being but I must remind you it isn t a good approach    WHERE              CASE LEN  TestPerson                   WHEN 0 THEN                          CASE WHEN co personentered    co personentered THEN 1 ELSE 0 END                 ELSE                          CASE WHEN co personentered LIKE   TestPerson  THEN 1 ELSE 0 END             END   1         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 CASE WHEN co DTEntered   co DTEntered THEN 1 ELSE 0 END                  ELSE                      CASE LEN  2012-01-09 11 56 29 327                            WHEN 0 THEN                              CASE WHEN co DTEntered  gt    2011-01-09 11 56 29 327  THEN 1 ELSE 0 END                          ELSE                              CASE WHEN co DTEntered BETWEEN  2011-01-09 11 56 29 327                                                           AND  2012-01-09 11 56 29 327                                                        THEN 1 ELSE 0 END                     END             END   1         AND tl storenum  lt  699

User · Answer

I think that the beginning of your query should look like that   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        WHEN  LEN  TestPerson     0 AND co personentered    co personentered  OR  LEN  TestPerson    lt  gt  0 AND co personentered LIKE     TestPerson  THEN 1       ELSE 0       END   1     AND    BUT  what is in the tail is completely not understandable

User · Answer

CASE LEN  TestPerson       WHEN 0 THEN co personentered    co personentered ELSE co personentered LIKE   TestPerson   Try the following      and         LEN  TestPerson     0 and co personentered    co personentered  or      LEN  TestPerson    lt  gt  0 and co personentered LIKE   TestPerson     and

User · Answer

You could also try like below eg  to show only Outbound Shipments     SELECT shp awb no shpr ctry cd  recvr ctry cd       CASE WHEN shpr ctry cd   record ctry cd       THEN  O        ELSE  I         END AS route      FROM shipment details     WHERE record ctry cd    JP        AND  O    CASE WHEN shpr ctry cd   record ctry cd                  THEN  O                   ELSE  I                    END

User · Answer

This works  declare  v int A select   from Table Name where XYZ 202  and  dbkey  case  v  when A then  Some Value 1  else  Some Value 2  end

User · Answer

select  d DISTNAME e BLKNAME a childid a studyingclass  from Tbl AdmissionRegister a inner join District master b on a Schooid b Schooid where  case when len  3601   4   then c distcd             when len  3601   6   then c blkcd              when len  3601   11  then c schcd   end    3601

User · Answer

select TUM1 userid TUM1 first name        TUM1 last name as NAME tum1 Business Title TUM1 manager id tum2 First Name         tum2 Last Name as  MANAGER NAME  TUM1 project TUM1 project code TUM1 rcc code TUM1 department TCM Company Name  case  when tum1 Gender ID 1 then  male   else  female   end  GENDER   tum1 Band as BAND  case when tum1 Inactive 0 then  STILL IN COMPANY  else  LEFT COMPANY   end  ACTIVE INACTIVE  from tbl user master TUM1 join tbl Company Master TCM on TCM Company Code TUM1 Company Code  join tbl User Master TUM2 on TUM1 Manager ID TUM2 UserID  where tum1 UserID in   54545414

User · Answer

SELECT   from TABLE                WHERE 1   CASE when TABLE col   100 then 1                       when TABLE col   200 then 2 else 3 END                    and TABLE col2    myname     Use in this way

User · Answer

Thanks for this question  actually I am looking for something else which is in below query  this may helps someone          SELECT DISTINCT CASE WHEN OPPORTUNITY    THEN   BLANK   ELSE OPPORTUNITY END   AS OPP LEN OPPORTUNITY  FROM  DBO   TBL    above query is to fill in dropdown which blank values shows as   blank    Also if we pass this value into sql where clause to get blank values with other values I don t know how to handle that  And finally came up with below solution this may helps to somebody   here it is     DECLARE  OPP TABLE  OPP VARCHAR 100     INSERT INTO  OPP VALUES   BLANK      UNFUNDED     FUNDED NOT COMMITTED   SELECT DISTINCT  OPPORTUNITY  FROM  DBO   TBL    WHERE       CASE WHEN   OPPORTUNITY     THEN   BLANK   ELSE OPPORTUNITY END IN  SELECT OPP FROM  OPP   ORDER BY 1

User · Answer

There WHERE part could be written like this   WHERE    LEN  TestPerson    lt  gt  0 OR co personentered    co personentered  AND   LEN  TestPerson     0 OR co personentered LIKE   TestPerson   AND   cc ccnum   CASE LEN  TestFFNum                   WHEN 0 THEN cc ccnum                  ELSE  TestFFNum                 END   AND   LEN  2011-01-09 11 56 29 327    lt  gt  0 OR co DTEntered   co DTEntered   AND    LEN  2011-01-09 11 56 29 327     0 AND LEN  2012-01-09 11 56 29 327    lt  gt  0  OR co DTEntered  gt    2011-01-09 11 56 29 327     AND    LEN  2011-01-09 11 56 29 327     0 AND LEN  2012-01-09 11 56 29 327     0  OR co DTEntered BETWEEN  2011-01-09 11 56 29 327  AND  2012-01-09 11 56 29 327     AND   tl storenum  lt  699

[sql] "CASE" statement within "WHERE" clause in SQL Server 2008

Examples related to sql

Examples related to sql-server

Examples related to case