CODEWITHSUNDEEP
sql-servert-sql
Robert Sievers
Robert Sievers

Reputation: 1355

WHERE inside CASE statement

I have seen lots of posts about putting CASE statements inside WHERE clauses. However, in my case, I want to put a WHERE statement inside a CASE statement, which I don't think can be done.

My situation is that I have a view that is going on many, many databases. In half of them, I need to have a WHERE clause. In the other half, I don't need the WHERE clause. Leaving it in isn't harmful, in that I don't get bad data, but it slows the query down considerably given that it has to read and sort a large table when it is unnecessary. 

Status_Table
ID    Status
1     Active
2     Inactive
3     Unknown    --this row of data will not exist in some DBs

Item_table
Item_ID    Status    value1    value2
1001          3      .....     .....
1002          1      .....     .....

What I want to do is something like this. 

-- big nasty ugly query with various CTEs, selects, and joins
CASE    WHEN (SELECT MAX(ID) FROM Status_table) = 3
        THEN WHERE Item_Table.Status != 3
        ELSE WHERE 1=1
END

Ultimately, I can probably swing this by constructing the query using dynamic SQL, but I was hoping for a more elegant solution.

Upvotes: 1

Views: 1538

Answers (4)

Devi Prasad
Devi Prasad

Reputation: 41

You can try this:

CASE WHEN (SELECT MAX(ID) FROM Status_table) = 3 
    THEN 3 
    ELSE (SELECT MAX(ID)+1 FROM Status_table) --Invalid value that will not exist
END <> Item_Table.Status

Upvotes: 0

TheKevlar
TheKevlar

Reputation: 1

If its performance your after start using DECLARE and SET. It only needs to execute ONCE!!!

DECLARE @UnkownExists AS INT
    SET @UnkownExists = (select count(*) from Status_table where ID = 3)

-- big nasty ugly query with various CTEs, selects, and joins
  WHERE (@UnkownExists=1 AND Item_Table.Status != 3) OR @NoUnknown=0

Upvotes: 0

Shawn
Shawn

Reputation: 4786

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Status_Table ( ID int, [Status] varchar(30) ) ;
INSERT INTO Status_Table ( ID, [Status] )
VALUES 
  ( 1,'Active' ), (2,'Inactive'), (3,'Unknown')
;

CREATE TABLE Item_table ( Item_ID int, [Status] int, value1 varchar(10), value2 varchar(10) ) ;
/* NOTE: Item_table.[Status] should be a descriptive name, like [StatusID], or something not the same name as a different datatype column in the relation table. */
INSERT INTO Item_Table ( Item_ID, [Status], value1, value2 )
VALUES (1001,3,'Bill','Exclude')
  ,(1002,1,'Ted','Include')
  ,(1003,3,'Rufus','Exclude')
  ,(1004,2,'Jay','Include')
  ,(1005,5,'Bob','BadRecord')
;

Query 1:

SELECT s1.Item_ID, s1.Status, s1.Value1, s1.Value2
FROM (
  SELECT i.Item_ID, i.Status, i.Value1, i.Value2
    , RANK() OVER (ORDER BY s.ID DESC) AS rn
  FROM Item_Table i
  RIGHT OUTER JOIN Status_Table s ON i.[Status] = s.ID
) s1
WHERE s1.rn <> 1

Results:

| Item_ID | Status | Value1 |  Value2 |
|---------|--------|--------|---------|
|    1004 |      2 |    Jay | Include |
|    1002 |      1 |    Ted | Include |

Upvotes: 0

digital.aaron
digital.aaron

Reputation: 5707

You're trying to build a conditional WHERE clause, yes? I'd think something like this would work pretty well for what you want to do:

WHERE (EXISTS (SELECT 1 FROM Status_table WHERE ID = 3) 
        AND Item_Table.Status != 3)
        OR
        NOT EXISTS (SELECT 1 FROM Status_table WHERE ID = 3)

Upvotes: 2

Related Questions