using case statement in a where clause
The branches of a case
expression can only return values, not additional expressions to be evaluated in the where
condition. You could, however, simulate this behavior with the and
and or
logical operators:
select *
from ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate
where (ScheduleDate = testdate) and
((HF.IsHoliday = 1 and overtime = 1 and makeup = 0) or
(overtime = 0 and Makeup = 0)) and
DOW = 5
order by ActivityStartTime
Note that you have makeup = 0
on both branches of the case
expression in the question (or both sides of the or
in the answer), so you could extract it out of it and simplify the condition a bit:
select *
from ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate
where ScheduleDate = testdate and
makeup = 0 and
((HF.IsHoliday = 1 and overtime = 1) or
overtime = 0) and
DOW = 5
order by ActivityStartTime
SQL Switch/Case in 'where' clause
declare @locationType varchar(50);
declare @locationID int;
SELECT column1, column2
FROM viewWhatever
WHERE
@locationID =
CASE @locationType
WHEN 'location' THEN account_location
WHEN 'area' THEN xxx_location_area
WHEN 'division' THEN xxx_location_division
END
SQL use CASE statement in WHERE IN clause
No you can't use case
and in
like this. But you can do
SELECT * FROM Product P
WHERE @Status='published' and P.Status IN (1,3)
or @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)
BTW you can reduce that to
SELECT * FROM Product P
WHERE @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)
since or P.Status IN (1,3)
gives you also all records of @Status='published' and P.Status IN (1,3)
SQL Server Case statement in WHERE Clause
As a case statement, you would write this as:
CASE WHEN f.PartName = 'B' and e.RecoverableFlag = 1 then 1
WHEN f.ParName = 'A' then 1
ELSE 0 END ) = 1
Is this the logic you want?
Many would think that the case statement is irrelevant here, and instead use:
WHERE ((f.PartName = 'B' and e.RecoverableFlag = 1) or (f.partName <> 'B')) . . .
Case statement in where clause to search for none specific values
It looks like you are just trying to do
where
(@value = 'A' and (option like '%gre%' or option like '%sma%')) or
(@value = 'B' and (option like '%tin%' or option like '%mic%')) or
(@value = 'C' and (option like '%gig%' or option like '%mul%')) ;
WHERE clause on CASE Statement alias
WHERE clause does not have any idea about aliased in SELECT list.
Reason: There's a logical processing order in MS SQL server (see link here) and in it WHERE is computed before the SELECT list
So one way is to create an inner query and put where outside.
Select * from
(
SELECT
ma.CustomerID,
ma.FirstName,
ma.LastName,
(
CASE WHEN (
sa.Active < 1
OR
ma.Active < 1
OR
(sa.CancelDate IS NOT NULL AND sa.CancelDate <= GETDATE())
OR
(ma.CancelDate IS NOT NULL AND ma.CancelDate <= GETDATE())
OR
(sa.ExpireDate IS NOT NULL AND DATEADD(dd, sa.Extension + 1, sa.ExpireDate) <= GETDATE())
) THEN
0
ELSE
1
END
) as IsAccountActive
FROM MasterAccounts ma
INNER JOIN SubAccounts sa
ON sa.CustomerID = ma.CustomerID
INNER JOIN MasterAccountData mad
ON mad.CustomerID = sa.CustomerID
WHERE mad.AccountDataTypeID = 20001
AND mad.Data = '')T
where T.IsAccountActive = 1
Another way is to put case in where clause like
SELECT
ma.CustomerID,
ma.FirstName,
ma.LastName
FROM MasterAccounts ma
INNER JOIN SubAccounts sa
ON sa.CustomerID = ma.CustomerID
INNER JOIN MasterAccountData mad
ON mad.CustomerID = sa.CustomerID
WHERE mad.AccountDataTypeID = 20001
AND mad.Data = ''
AND CASE WHEN (
sa.Active < 1
OR
ma.Active < 1
OR
(sa.CancelDate IS NOT NULL AND sa.CancelDate <= GETDATE())
OR
(ma.CancelDate IS NOT NULL AND ma.CancelDate <= GETDATE())
OR
(sa.ExpireDate IS NOT NULL AND DATEADD(dd, sa.Extension + 1, sa.ExpireDate) <= GETDATE()))
THEN
0
ELSE
1
END =1
SQL Case statement In Where clause: Msg 102, Level 15, State 1
Just use simple logic:
WHERE (@Platform = 1 AND [Windows_PC] = 1) OR
(@Platform = 2 AND [Mac] = 1) OR
(@Platform = 3 AND [Linux] = 1) OR
(@Platform = 4 AND [Android] = 1) OR
(@Platform NOT IN (1, 2, 3, 4) AND [Windows_PC] = 1)
The condition for "1" is redundant with the else
. I'm not sure if that is a typo. If not, you can remove the first condition.
Related Topics
Optimising a Select Query That Runs Slow on Oracle Which Runs Quickly on SQL Server
How to Convert Result of an Select SQL Query into a New Table in Ms Access
How to Retrieve The Primary Key When Saving a New Object in Anorm
Efficiently Duplicate Some Rows in Postgresql Table
Joining Multiple Common Table Expressions
Replace Multiple Characters in Sql
Selecting Most Recent Date Between Two Columns
How to Use a Case Statement in Scalar Valued Function in Sql
Failing Update Table in Db2 with Sqlcode: -668, Sqlstate: 57016, Sqlerrmc: 7;
Creating SQL Table Using Dynamic Variable Name
How to Use MySQL Index Columns
Sql: How to Get All The Distinct Characters in a Column, Across All Rows
Is Cross Join a Synonym for Inner Join Without on Clause
When SQL Developer Is Idle I Lose My Connection