jgoodale
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.
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.
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)
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.
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..
ASKER
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 a.[id] AS agency_id,
a.agency_name,
Count(ti.id) AS tran_total,
Convert(DATE,Cast(Month(a.
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)
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.
>> SELECT OrderId FROM Splitorderids(@agencyID) AS agencylist
is there any difference between the two subqueries in terms of resultant records.
ASKER
Yes,
Think of agencies as small companies in an area and divisions are their parent category.
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
ASKER
I think there is an extra ')' after each end but when I ran the query it didn't return any results.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm still getting 0 record count for the results
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for all your help!
Welcome..
And glad to help you out..
And glad to help you out..
>> 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..
Open in new window