Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Subquery returned more than 1 value error

Author  Topic 

kdeutsch
Yak Posting Veteran

83 Posts
Posted - 2009-05-20 : 13:36:00
I get this error when I add in the last part of my code I need for the whole query. Before I added it it worked just fine.

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.

This is the main query with the added stuff with a question mark next to it. If I take this stuff out it works
fine, and the added code works fine by itself, but for some reason together they don't work and I need them to
play nice together.

SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
???? (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,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type,
(Select case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end from tblStatUIC where st.dtExpire > Getdate() and st.strUIC = ut.UPC) as UNIT_Stat
FROM SIDPERS..SIDPERS.PERS_UNIT_TBL as ut Inner join
SIDPERS..SIDPERS.PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
???? recruitDotNet.dbo.tblACNRequest as ac on ac.strUIC = at.UPC Left Outer Join
RecruitDotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N' or ut.OESTS = 'R'
Group By at.UPC, ut.Addr_City, at.Auth_Para_Dsg, at.Auth_Line_Dsg, at.GRADE, at.POSC, at.AUTH_PERS_IDENT, at.AUTH_STR, at.AUTH_DOC_POSN_TITLE,
at.ASGN_STR, ac.strUic, ac.strPara, ac.strLine, at.DOC_NBR, st.strStat, ut.UPC, ac.dtExpire, ac.dtCancelACN, st.dtExpire, st.strUIc
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg


Here is the code by itself with select info from above to make it count the ACN's in that table.

Select at.UPC as UIC, (Duplicate form Query above to make count work)
at.Auth_Para_Dsg as PARA, (Duplicate form Query above to make count work)
at.Auth_Line_Dsg as LINE, (Duplicate form Query above to make count work)
(Select(Count(ac.strAcn)) 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, ac.struic, ac.strPara, ac.strLine, ac.dtExpire, ac.dtCancelACn

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-20 : 13:44:23
quote:
Originally posted by kdeutsch

I get this error when I add in the last part of my code I need for the whole query. Before I added it it worked just fine.

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.

This is the main query with the added stuff with a question mark next to it. If I take this stuff out it works
fine, and the added code works fine by itself, but for some reason together they don't work and I need them to
play nice together.

SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
???? (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,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type,
(Select case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end from tblStatUIC where st.dtExpire > Getdate() and st.strUIC = ut.UPC) as UNIT_Stat
FROM SIDPERS..SIDPERS.PERS_UNIT_TBL as ut Inner join
SIDPERS..SIDPERS.PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
???? recruitDotNet.dbo.tblACNRequest as ac on ac.strUIC = at.UPC Left Outer Join
RecruitDotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N' or ut.OESTS = 'R'
Group By at.UPC, ut.Addr_City, at.Auth_Para_Dsg, at.Auth_Line_Dsg, at.GRADE, at.POSC, at.AUTH_PERS_IDENT, at.AUTH_STR, at.AUTH_DOC_POSN_TITLE,
at.ASGN_STR, ac.strUic, ac.strPara, ac.strLine, at.DOC_NBR, st.strStat, ut.UPC, ac.dtExpire, ac.dtCancelACN, st.dtExpire, st.strUIc
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg


Here is the code by itself with select info from above to make it count the ACN's in that table.

Select at.UPC as UIC, (Duplicate form Query above to make count work)
at.Auth_Para_Dsg as PARA, (Duplicate form Query above to make count work)
at.Auth_Line_Dsg as LINE, (Duplicate form Query above to make count work)
(Select(Count(ac.strAcn)) 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, ac.struic, ac.strPara, ac.strLine, ac.dtExpire, ac.dtCancelACn
It looks like the issue is with the correlated sub-query I highlighted.

Did you look at the responses to your other posts about this same issue?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126019
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125717
Go to Top of Page

kdeutsch
Yak Posting Veteran

83 Posts
Posted - 2009-05-20 : 14:34:27
Hi,
Yes those where different parts of the same query, that have been corrected, this is the latest add on. What I found out is this I posted you the wrong query. This is the correct one with added code.

SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
(Select(Count(ac.strAcn)) 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,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type,
(Select case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end from tblStatUIC where st.dtExpire > Getdate() and st.strUIC = ut.UPC) as UNIT_Stat
FROM SIDPERS..SIDPERS.PERS_UNIT_TBL as ut Inner join
SIDPERS..SIDPERS.PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
recruitDotNet.dbo.tblACNRequest as ac on ac.strUIC = at.UPC Left Outer Join
RecruitDotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N' or ut.OESTS = 'R'
Group By at.UPC, ut.Addr_City, at.Auth_Para_Dsg, at.Auth_Line_Dsg, at.GRADE, at.POSC, at.AUTH_PERS_IDENT, at.AUTH_STR, at.AUTH_DOC_POSN_TITLE,
at.ASGN_STR, ac.strUic, ac.strPara, ac.strLine, at.DOC_NBR, st.strStat, ut.UPC, ac.dtExpire, ac.dtCancelACN, st.dtExpire, st.strUIc
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg


It is this part that is giving me the trouble.
(Select(Count(ac.strAcn)) 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,

I put it back into the little query and found out its duplicating all the counts as such
TSZHD 600 02 1
TSZHD 600 02 1
TSZHD 600 03 4
TSZHD 600 03 4

This is correct for a count TSZHD 600 03 4 but its duplicated twice and they are all like this except the ones that do not have a count.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-20 : 14:39:46
Is that SELECT COUNT giving you incorrect results? Or are you still getting the error on the other correlated sub-query that can return more than one row?
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2009-05-20 : 14:45:25
Hard to think a SELECT COUNT with no GROUP BY clause can give more than one result; I think the problem is here:
Select case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end from tblStatUIC where st.dtExpire > Getdate() and st.strUIC = ut.UPC

It's possible for this to return more than one result. Try using a TOP 1 clause in it as such:
Select top 1 case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end from tblStatUIC where st.dtExpire > Getdate() and st.strUIC = ut.UPC

See if that makes a difference.


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

kdeutsch
Yak Posting Veteran

83 Posts
Posted - 2009-05-20 : 14:48:19
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. but when I put the 2 lines in that I need the main query wants me to put in everything in the group by clause which then throws the error. Here are the 2 I am adding to main query.

(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

tblACNRequest as ac on ac.strUIC = at.UPC
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-20 : 15:06:51
Post some DDL and Sample data with Expected output and we can help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

kdeutsch
Yak Posting Veteran

83 Posts
Posted - 2009-05-20 : 15:18:39
jholovacs,
Thank YOU Thank YOU Thank YOU Thank YOU Thank YOU Thank YOU Thank YOU, I have been punding my head on this too long. It works with the top clause in.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 2009-05-21 : 07:31:44
Of course that will make it work, but which value does it actually return when there are more than one? You need to decide which you want and solve the problem properly. BTW - TOP is rarely the answer without an order by.
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2009-05-21 : 08:59:27
Agreed, you might have unexpected or undesired results with that; Now that you have localized the problem, you need to apply an ORDER BY clause to your subquery to make the results from that subquery as deterministic as possible.


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

kdeutsch
Yak Posting Veteran

83 Posts
Posted - 2009-05-21 : 09:23:05
Yes, you are both correct and I have already done so and am getting my desired results form the query. Thanks for the help
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site