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…
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)
Is there a better way to accomplish my task, or is there a way to fix my current attempted solution?
select * from #temptable FT where (@siteid='CF' and (FT.InvtID not like 'C-%' or FT.InvtID='C-20042')) or @siteid<>'CF'