Link to home
Start Free TrialLog in
Avatar of jgoodale
jgoodaleFlag for United States of America

asked on

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.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

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

Avatar of jgoodale

ASKER

That works for the first ID but if there are more ID's in the list they are ignored.
Ok.. Just replace * with only one column name which should work..
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
>> 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.
Yes,

Think of agencies as small companies in an area and divisions are their parent category.
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

I think there is an extra ')' after each end but when I ran the query it didn't return any results.
SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm still getting 0 record count for the results
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for all your help!
Welcome..
And glad to help you out..