Variable in IN clause

I have a query in MS SQL 2008 and its working except in the case that multiple agencies are passed to the stored procedure. I have test the function and it does create the table properly but the error that I'm getting back is below:

Msg 512, Level 16, State 1, Procedure select_temp_report, Line 17
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 the query:

                  select
                    a.[id] as agency_id,
                    a.agency_name,
                    COUNT(ti.id) as tran_total,
                    Convert(date,CAST(Month(A.Thedate) as varchar(2)) + '/' + CAST(Day(A.Thedate) as varchar(2)) + '/' + Cast(Year(A.Thedate) as varchar(4)),101) as dReceived
                  from  
                    (select *
                         from agencies AS A,
                         (
                     Select convert(datetime,d) as Thedate
                         from (select distinct tiDate.dReceived as d from transactions_incoming tiDate where tiDate.dReceived between @startDate and @endDate) as x
                    ) as D
                        WHERE A.[id] IN (
                              CASE WHEN @agencyID='0' THEN
                                    A.[ID]
                              Else
                                    (select * from splitOrderIDs(@agencyID) as agencyList)
                              End
                     )
                     AND
                     A.[agency_division_id] IN (
                              CASE WHEN @divisionID='0' THEN
                                    A.[agency_division_id]
                              Else
                                    (select OrderID from splitOrderIDs(@divisionID) as divisionList)
                              End      
                     )
                     ) as a
                    LEFT OUTER JOIN
                    (select *
                           from transactions_incoming
                        ) as ti
                    on a.id=ti.agency_id
                   AND ti.dReceived = A.thedate
                  Group By
                        a.id,
                        a.agency_name,
                        Year(A.Thedate),
                        Month(A.Thedate),
                        Day(A.Thedate)
                  order by 4,1

The reason for the case statement is if the user wants all agencies.
jgoodaleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Modified code attached:

>> select * from splitOrderIDs(@agencyID) as agencyList

Replace * with TOP 1 column_name where column_name would be any column name out of the subquery to get it work..
select
                    a.[id] as agency_id,
                    a.agency_name,
                    COUNT(ti.id) as tran_total,
                    Convert(date,CAST(Month(A.Thedate) as varchar(2)) + '/' + CAST(Day(A.Thedate) as varchar(2)) + '/' + Cast(Year(A.Thedate) as varchar(4)),101) as dReceived
                  from  
                    (select *
                         from agencies AS A,
                         (
                     Select convert(datetime,d) as Thedate
                         from (select distinct tiDate.dReceived as d from transactions_incoming tiDate where tiDate.dReceived between @startDate and @endDate) as x
                    ) as D
                        WHERE A.[id] IN (
                              CASE WHEN @agencyID='0' THEN
                                    A.[ID]
                              Else
                                    (select * from splitOrderIDs(@agencyID) as agencyList)
                              End
                     )
                     AND
                     A.[agency_division_id] IN (
                              CASE WHEN @divisionID='0' THEN
                                    A.[agency_division_id]
                              Else
                                    (select top 1 OrderID from splitOrderIDs(@divisionID) as divisionList)
                              End      
                     )
                     ) as a
                    LEFT OUTER JOIN transactions_incoming ti on a.id=ti.agency_id
                   AND ti.dReceived = A.thedate
                  Group By
                        a.id,
                        a.agency_name,
                        Year(A.Thedate),
                        Month(A.Thedate),
                        Day(A.Thedate)
                  order by 4,1

Open in new window

0
jgoodaleAuthor Commented:
That works for the first ID but if there are more ID's in the list they are ignored.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Ok.. Just replace * with only one column name which should work..
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

jgoodaleAuthor Commented:
I get the same error if I replace the * with the field OrderId

            SELECT   a.[id]       AS agency_id,
                         a.agency_name,
                         Count(ti.id) AS tran_total,
                         Convert(DATE,Cast(Month(a.thedate) AS VARCHAR(2)) + '/' + Cast(Day(a.thedate) AS VARCHAR(2)) + '/' + Cast(Year(a.thedate) AS VARCHAR(4)),
                                     101) AS dreceived
            FROM     (SELECT *
                          FROM   agencies AS a,
                                     (SELECT Convert(DATETIME,d) AS thedate
                                      FROM   (SELECT DISTINCT tidate.dreceived AS d
                                                  FROM   transactions_incoming tidate
                                                  WHERE  tidate.dreceived BETWEEN @startDate AND @endDate) AS x) AS d
                          WHERE  a.[id] IN (CASE
                                                        WHEN @agencyID = '0'
                                                        THEN a.[ID]
                                                        ELSE (SELECT OrderId
                                                                  FROM   Splitorderids(@agencyID) AS agencylist)
                                                      END)
                                     AND a.[agency_division_id] IN (CASE
                                                                                      WHEN @divisionID = '0'
                                                                                      THEN a.[agency_division_id]
                                                                                      ELSE (SELECT orderid
                                                                                                FROM   Splitorderids(@divisionID) AS divisionlist)
                                                                                    END)) AS a
                         LEFT OUTER JOIN (SELECT *
                                                  FROM   transactions_incoming) AS ti
                           ON a.id = ti.agency_id
                                AND ti.dreceived = a.thedate
            GROUP BY a.id,
                         a.agency_name,
                         Year(a.thedate),
                         Month(a.thedate),
                         Day(a.thedate)
            ORDER BY 4,
                         1
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> SELECT orderid FROM   Splitorderids(@divisionID) AS divisionlist
>> SELECT OrderId FROM   Splitorderids(@agencyID) AS agencylist

is there any difference between the two subqueries in terms of resultant records.
0
jgoodaleAuthor Commented:
Yes,

Think of agencies as small companies in an area and divisions are their parent category.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try this;
select
    a.[id] as agency_id,
    a.agency_name,
    COUNT(ti.id) as tran_total,
    Convert(date,CAST(Month(A.Thedate) as varchar(2)) + '/' + CAST(Day(A.Thedate) as varchar(2)) + '/' + Cast(Year(A.Thedate) as varchar(4)),101) as dReceived
  from  
    (select *
         from agencies AS A,
         (
     select distinct convert(datetime,tiDate.dReceived) as Thedate from transactions_incoming tiDate where tiDate.dReceived between @startDate and @endDate
    ) as D
        WHERE A.[id] IN (
              SELECT ID FROM (
              SELECT A.[ID], '0' as temp_val
              FROM agencies 
              UNION ALL 
              SELECT OrderId, '1' 
              from splitOrderIDs(@agencyID)) temp where temp_val = CASE when @agencyID= '0' then '0' else '1' end) 
     )
     AND
     A.[agency_division_id] IN (
              SELECT ID FROM (
              SELECT A.[agency_division_id], '0' as temp_val
              FROM agencies 
              UNION ALL 
              SELECT OrderId, '1' 
              from splitOrderIDs(@divisionID)) temp where temp_val = CASE when @divisionID= '0' then '0' else '1' end) 
     )
     
     ) as a
    LEFT OUTER JOIN transactions_incoming ti on a.id=ti.agency_id
   AND ti.dReceived = A.thedate
  Group By
        a.id,
        a.agency_name,
        Year(A.Thedate),
        Month(A.Thedate),
        Day(A.Thedate)
  order by 4,1

Open in new window

0
jgoodaleAuthor Commented:
I think there is an extra ')' after each end but when I ran the query it didn't return any results.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try this:
declare @startDate datetime,
@endDate datetime,
@agencyID int,
@divisionID int
select
    a.[id] as agency_id,
    a.agency_name,
    COUNT(ti.id) as tran_total,
    Convert(date,CAST(Month(A.Thedate) as varchar(2)) + '/' + CAST(Day(A.Thedate) as varchar(2)) + '/' + Cast(Year(A.Thedate) as varchar(4)),101) as dReceived
  from  
    (select *
         from agencies AS A,
         (
     select distinct convert(datetime,tiDate.dReceived) as Thedate from transactions_incoming tiDate where tiDate.dReceived between @startDate and @endDate
    ) as D
        WHERE A.[id] IN (
              SELECT ID FROM (
              SELECT A.[ID], '0' as temp_val
              FROM agencies 
              UNION ALL 
              SELECT OrderId, '1' 
              from splitOrderIDs(@agencyID)) temp where temp_val = CASE when @agencyID= '0' then '0' else '1' end) 
     AND
     A.[agency_division_id] IN (
              SELECT ID FROM (
              SELECT A.[agency_division_id], '0' as temp_val
              FROM agencies 
              UNION ALL 
              SELECT OrderId, '1' 
              from splitOrderIDs(@divisionID)) temp where temp_val = CASE when @divisionID= '0' then '0' else '1' end) 
    
     ) as a
    LEFT OUTER JOIN transactions_incoming ti on a.id=ti.agency_id
   AND ti.dReceived = A.thedate
  Group By
        a.id,
        a.agency_name,
        Year(A.Thedate),
        Month(A.Thedate),
        Day(A.Thedate)
  order by 4,1

Open in new window

0
jgoodaleAuthor Commented:
I'm still getting 0 record count for the results
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you kindly check the results of this modified subqueries..

SELECT ID FROM (
              SELECT A.[ID], '0' as temp_val
              FROM agencies  
              UNION ALL  
              SELECT OrderId, '1'  
              from splitOrderIDs(@agencyID)) temp where temp_val = CASE when @agencyID= '0' then '0' else '1' end

and

SELECT ID FROM (
              SELECT A.[agency_division_id], '0' as temp_val
              FROM agencies  
              UNION ALL  
              SELECT OrderId, '1'  
              from splitOrderIDs(@divisionID)) temp where temp_val = CASE when @divisionID= '0' then '0' else '1' end
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jgoodaleAuthor Commented:
I think I found the problem. In the second subquery, Its starts with "SELECT ID FROM" but there is no column name "ID" in that query. I changed that select to "SELECT A.[agency_division_id]" and it seems to work. I'm gonna do some testing but I think that you got me there! Thanks!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, that was a typo and replace with the modified one and give it a try.

SELECT agency_division_id FROM (
              SELECT A.[agency_division_id] , '0' as temp_val
              FROM agencies  
              UNION ALL  
              SELECT OrderId, '1'  
              from splitOrderIDs(@divisionID)) temp where temp_val = CASE when @divisionID= '0' then '0' else '1' end
0
jgoodaleAuthor Commented:
Thank you for all your help!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
And glad to help you out..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.