Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sub-Select returning a more than 1 value Error

Posted on 2009-05-20
12
Medium Priority
?
693 Views
Last Modified: 2012-05-07
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.
See text file for all code.

Open in new window

0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 9

Accepted Solution

by:
ezraa earned 2000 total points
ID: 24434007
You need to make sure that the sub query you added is only returning one record.  You can use "select top 1" or "select sum()" to ensure that one value only is being returned.

Without seeing your query, we cannot offer you much more assistance.
0
 

Author Comment

by:kdeutsch
ID: 24434031
Sorry forgot to attach my code.
Help.txt
0
 

Author Comment

by:kdeutsch
ID: 24434120
Here is a follow up text file and data selecting form the querys.
Help2.txt
0
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24434138
You are missing a FROM clause in the subquery.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24434147
Never mind that is not it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24434201
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,
0
 
LVL 9

Expert Comment

by:ezraa
ID: 24434273
I think the issue is the fields you are grouping in the recruitDotNet.dbo.tblACNRequest as ac table are causing the subquery to return multiple results.

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

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 24434374
HI,
Ok I changed to this line
(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,

deleted this line
recruitDotNet.dbo.tblACNRequest 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
0
 

Author Comment

by:kdeutsch
ID: 24434559
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
0
 

Author Comment

by:kdeutsch
ID: 24434994
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24436940
So why don't you change this:
Select Count(*) from recruitDotNet.dbo.tblACNRequest 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.tblACNRequest ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.dtExpire >= getdate() and ac.dtCancelACN is null
0
 

Author Closing Comment

by:kdeutsch
ID: 31583602
After adding top 1 to code and redoing the group by it worked fine
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question