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?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. Just replace * with only one column name which should work..
0
 
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 & ArchitectCommented:
>> 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 & ArchitectCommented:
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 RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
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 RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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 & ArchitectCommented:
Welcome..
And glad to help you out..
0
All Courses

From novice to tech pro — start learning today.