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
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
Using Case statement in Where clause in Oracle SQL
Your question is a bit ambiguous. I have assumed that country is an attribute in the table employees of data type VARCHAR.
SELECT * FROM employees
WHERE
(emp_id = v_emp_id AND country = 'USA')
OR (emp_id <= v_emp_id AND country != 'USA')
You might want to take a look at WHERE, OR and AND.
Quoting the OR page linked above:
If you use multiple logical operators in a statement, Oracle evaluates the OR operators after the NOT and AND operators. However, you can change the order of evaluation by using parentheses.
Use a CASE expression Column value in another CASE expression in SQL Query
You can't reuse an expression defined in the select clause in the same clause.
I don't really see why you need the value returned by the first expression to compute the second value, since the logic is basically the same.
You can just do:
select
case
when user1 = 1 then then 'Approve'
when user2 = 2 then 'Reject'
when user3 = 3 then 'Pending Decision'
else 'error'
end status,
case
when user1 = 1 then (select username1 from where userid = )
when user2 = 2 then (select username2 from where userid = )
when user3 = 3 then (select username3 from where userid = )
end username
from where userid =
Notes
isnull() is unnecessary, since the alternative value is not trapped anywhere in the case statement
this treats user1 as a number, because it looks like it - so I unquoted the test values in the case statement. If it's actually a string, you can revert this and add the single quotes.
I am quite suspicious about the logic of the subqueries; there might be ways to simplify that, if you were to provide more details on what they are intented to do, along with sample data and desired results.
Using a case column within another case in select clause
The simplest way is to use a subquery that returns the column discount_rule:
select t.client, t.discount, t.discount_rule,
case
when discount < discount_rule then 1
else 0
end status
from (
select client, discount,
case
when sales_avg > 10000 then 30
when sales_avg > 5000 then 20
else 0
end discount_rule
from sales
) t
Select criteria using a case expression in where clause based on other columns
SELECT ID,SUM(CASE WHEN Level=1 AND Grade IN ('A','B','C','D') THEN 1
WHEN Level=2 AND Grade IN ('A','B','C') THEN 1
ELSE 0
END AS GradeCount
FROM YourTable
GROUP BY ID;
Related Topics
MySQL Select Dynamic Row Values as Column Names, Another Column as Value
Create a Pivot Table With Postgresql
Is There a Max Function in SQL Server That Takes Two Values Like Math.Max in .Net
MySQL Insert into Table Values.. VS Insert into Table Set
Is There a Shortcut For Select * From
Operand Should Contain 1 Column - MySQL Not In
Most Efficient T-SQL Way to Pad a Varchar on the Left to a Certain Length
Unknown Column in 'Field List' Error on MySQL Update Query
MySQL Unknown Column in on Clause
SQL Server 2005 Pivot on Unknown Number of Columns
Fastest Way to Count Exact Number of Rows in a Very Large Table
What Are Best Practices For Multi-Language Database Design
Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server