How to Use a Case Statement in a SQL from Clause

Is it possible to use a Case statement in a sql From clause

Assuming SQL Server:

You would need to use dynamic SQL. Build the string and then call sp_executesql with the string.

Edit: Better yet, just use if statements to execute the appropriate statement and assign the value to a variable. You should avoid dynamic SQL if possible.

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

Is it possible in from clause use case when statement

No, you can't do that. The objects and identifiers have to be fixed when the query is parsed, so you can't bind those.

You could use a union with a check for the variable in each branch:

select * from t1
where :p1 = 1
union all 
select * from t2
where :p1 = 2;

assuming the structure of the tables is the same.

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)

How to use CASE statement inside a WHERE with an IN clause?

You could solve this by using OR instead of CASE:

SELECT *
FROM table
WHERE (@showListedOrSold = 0 AND id IN (1, 2, 5, 6, 10, 11))
        OR (@showListedOrSold = 1 AND id IN (1, 5, 6, 10, 11))
        OR (@showListedOrSold = 2 AND id IN (2))

Is there a way to possibly put a case statement in the from clause?

You cannot do this. Here is a sort-of-close method:

select ab.*
from ((select a.*
       from stockA a
       where @location = 'location A'
      ) union all
      (select b.*
       from stockB b
       where @location = 'location B'
      )
     ) ab

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

---

Related Topics