'Using a subquery in a case statement in a where clause
I want to select different results (from a temp table) based on what a certain parameter is set to in the query. I want to exclude certain inventory SKUs if a particular warehouse is selected. I am getting this error, unfortunately.
Msg 512, Level 16, State 1, Line 310 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is a simplified temp table I am selecting from for example...
InvtID |
---|
C-123 |
C-789 |
C-20042 |
12345 |
56789 |
35353 |
declare @siteid VARCHAR(10) = 'CF'
select *
from #temptable
where InvtID in (Case when @siteid='CF' then (
select I.InvtID
from dbo.Inventory I
where I.InvtID not like 'C-%'
union all
select 'C-20042')
else (select I.InvtID from dbo.Inventory I) end)
expected result
InvtID |
---|
C-20042 |
12345 |
56789 |
35353 |
Is there a better way to accomplish my task, or is there a way to fix my current attempted solution?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|