December 12, 2007 at 8:48 am
Hello,
I am trying to create dynamic WHERE IN statement.
declare @ReportType TINYINT
set @ReportType=1
SELECT SUM(EnrollCnt)
FROM ReportA
WHERE [Year] = 2006
AND BranchID IN (
CASE @ReportType
WHEN 1 THEN ('10000023')
WHEN 2 THEN (SELECT BranchID FROM Branch WHERE GMOfficeID = '10000006')
END )
When @ReportType=1 it works, but when @ReportType=2
it gives error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Sure, subquery should return more than 1 value, this is the point!
Any thoughts? Thank you.
December 12, 2007 at 8:59 am
If you must do this inside the SQL statement (and would not like to use a top level if else statement, you
could do something like:
SELECT SUM(EnrollCnt)
FROM ReportA
WHERE [Year] = 2006
AND BranchID IN ( SELECT BranchID
FROM Branch
WHERE GMOfficeID = '10000006'
AND 1 = @ReportType
UNION ALL
SELECT '10000023' AS BranchID
WHERE 2 = @ReportType )
Regards,
Andras
December 12, 2007 at 9:00 am
Move this into an INNER JOIN
SELECT SUM(EnrollCnt)
FROM ReportA
INNER JOIN Branch
ON (@ReportType = 1 AND BranchID = '10000023')
OR
(@ReportType = 2 AND GMOfficeID = '10000006')
WHERE [Year] = 2006
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 12, 2007 at 9:06 am
Wow, what a fast response.
Thank you, Andras, for good idea.:)
December 12, 2007 at 9:12 am
The problem is that subqueries can't return multiple values in some cases. Your data probably violates that. Try one of the suggestions above.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 12, 2007 at 9:43 am
Here is another way to do it that keeps the case statement instead of a union. There is almost always more than one way to handle the problem:
declare @ReportType TINYINT
set @ReportType=1;
with CTE1 as (
select
case
when @ReportType = '1' then '10000006'
when @ReportType = '2' then BranchID
end as Branchid
From
ReportA
where
[Year] = 2006
and (GMOfficeID = '10000006' or @ReportType = '1')
)
SELECT
SUM(EnrollCnt)
FROM
ReportA
WHERE
[Year] = 2006
AND BranchID IN (select * from CTE1 )
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 12, 2007 at 9:45 am
My opinion is the INNER JOIN method above is more straight forward, but then again I'm biased 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 12, 2007 at 11:37 am
Thank you one more time. I think that example with CTE1 is most flexible.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply