kdeutsch
asked on
Sub-Select returning a more than 1 value Error
I have my main query which I have been slowy adding to, to get the desired results. I added in most recent piece and I get the follwoing error and cannot seem to get past it.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
See text file for all code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is a follow up text file and data selecting form the querys.
Help2.txt
Help2.txt
You are missing a FROM clause in the subquery.
Never mind that is not it.
On second thought you do appear to be missing a FROM clause in the following subquery:
(Select (Count(ac.strAcn)) where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and ac.dtExpire >= getdate() and ac.dtCancelACN is null) as ACN,
(Select (Count(ac.strAcn)) where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and ac.dtExpire >= getdate() and ac.dtCancelACN is null) as ACN,
I think the issue is the fields you are grouping in the recruitDotNet.dbo.tblACNRe quest as ac table are causing the subquery to return multiple results.
You should rewrite the subquery
You should rewrite the subquery
(Select Count(ac.strAcn) from recruitDotNet.dbo.tblACNRequest as ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and ac.dtExpire >= getdate() and ac.dtCancelACN is null) as ACN,
also you will need to remove the ac table from the main query and remove references to it in the GROUP BY clause
ASKER
HI,
Ok I changed to this line
(Select Count(ac.strAcn) from recruitDotNet.dbo.tblACNRe quest as ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and ac.dtExpire >= getdate() and ac.dtCancelACN is null) as ACN,
deleted this line
recruitDotNet.dbo.tblACNRe quest as ac on ac.strUIC = at.UPC Left Outer Join
and all references in the group by clause, but I still got this error
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
minus the null thing
Ok I changed to this line
(Select Count(ac.strAcn) from recruitDotNet.dbo.tblACNRe
deleted this line
recruitDotNet.dbo.tblACNRe
and all references in the group by clause, but I still got this error
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
minus the null thing
ASKER
ok,
did further research and I found what it is doing here is what I returned from my smaller query when I changed it to as requested. Id is duplicating the counts twice for the same UIC, Para and Line. It is all depended on the UIC, Para and Line matching to get the count.
UIC para line count
TSZHD 600 02 1
TSZHD 600 02 1
TSZHD 600 03 4
TSZHD 600 03 4
did further research and I found what it is doing here is what I returned from my smaller query when I changed it to as requested. Id is duplicating the counts twice for the same UIC, Para and Line. It is all depended on the UIC, Para and Line matching to get the count.
UIC para line count
TSZHD 600 02 1
TSZHD 600 02 1
TSZHD 600 03 4
TSZHD 600 03 4
ASKER
OK,
I have the small one working as it needs to counting and no duplication, its all in the group by clause that was why duplication.
Select at.UPC as UIC,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
(Select(Count(ac.intAcnId) ) from tblACNREquest as ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and dtExpire >= getdate() and dtCancelACN is null) as ACN
from SIDPERS..SIDPERS.PERS_AUTH _STR_TBL as at Left Outer join
tblACNRequest as ac on ac.strUIC = at.UPC
Group by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
Order by at.UPC
But the problem is when I put it into the big query it goes loopy and tells me to add every filed to the group by clause which then makes it through the error.
I have the small one working as it needs to counting and no duplication, its all in the group by clause that was why duplication.
Select at.UPC as UIC,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
(Select(Count(ac.intAcnId)
from SIDPERS..SIDPERS.PERS_AUTH
tblACNRequest as ac on ac.strUIC = at.UPC
Group by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
Order by at.UPC
But the problem is when I put it into the big query it goes loopy and tells me to add every filed to the group by clause which then makes it through the error.
So why don't you change this:
Select Count(*) from recruitDotNet.dbo.tblACNRe quest ac where ac.strUic = at.UPC and ac.dtExpire >= getdate() and ac.dtCancelACN is null
Or depending if you want to include para as well::
Select Count(*) from recruitDotNet.dbo.tblACNRe quest ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.dtExpire >= getdate() and ac.dtCancelACN is null
Select Count(*) from recruitDotNet.dbo.tblACNRe
Or depending if you want to include para as well::
Select Count(*) from recruitDotNet.dbo.tblACNRe
ASKER
After adding top 1 to code and redoing the group by it worked fine
ASKER
Help.txt