Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression.

Question

0.00/5 (No votes)

See more:

Hi,

I am getting the following error while I am trying to execute the following query..

'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'

SQL

DECLARE @UserId INT 
DECLARE @OriginatorId Varchar(16) 
SET @UserId=7516
SET @OriginatorId=33647


INSERT INTO dbo.tblAccount
( 
		UserID_Created , UserID_Assigned ,OriginatorID , OrigAccountID , AccountName , StatusTypeID ,DateCreated , AccountTypeID ,OnlineID , 
		IsLockedForOffline ,
		ConvertedByUserID , DateConvertedFromLead ,ExternalAccountID
)	
SELECT @UserId,@UserId,@OriginatorId,FileKey,'Account '+CAST(FileKey AS VARCHAR),-1,GETDATE(),972, -1,	0,	null,NULL,NULL
FROM dbo.ApplicantDetails
GROUP BY FileKey
having FileKey IN ('8','9')

Can anyone help?

Thanks
Nasif

Posted 30-May-13 16:12pm

NASIFKHAN

Updated 30-May-13 16:52pm

walterhevedeich

v2

Add a Solution

Comments

Prasaad SJ 30-May-13 22:21pm

At which line number you are getting this error.

NASIFKHAN 31-May-13 0:31am

Basically the error is in the query ...
SELECT @UserId,@UserId,@OriginatorId,FileKey,'Account '+CAST(FileKey AS VARCHAR),-1,GETDATE(),972, -1, 0, null,NULL,NULL
FROM dbo.ApplicantDetails
GROUP BY FileKey
having FileKey IN ('8','9')

I need to rewrite it ... but don't know how to do it..

If I ran the query separately, the query is ok

Thanks
Nasif

Arun Vasu 31-May-13 0:42am

while running this query what will be the output?
SELECT @UserId,@UserId,@OriginatorId,FileKey,'Account '+CAST(FileKey AS VARCHAR),-1,GETDATE(),972, -1, 0, null,NULL,NULL
FROM dbo.ApplicantDetails
GROUP BY FileKey
having FileKey IN ('8','9')

pls show me..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pradip Jadhav, India · Answer 1 · 2013-05-30T19:11:00

Solution 2

write VALUES before select in your query

Posted 30-May-13 19:11pm

Pradip Jadhav, India

amnk.info · Answer 2 · 2013-05-30T19:28:00

Solution 3

see these examples

http://stackoverflow.com/questions/2653188/sql-server-subquery-returned-more-than-1-value-this-is-not-permitted-when-the-s[^]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177065[^]

Posted 30-May-13 19:28pm

amnk.info

SudhirKankal · Answer 3 · 2013-05-30T20:27:00

As a beginner every person lands up in getting this kind of error. Alternative is to re-write the SQL to suite the requirement. Using such sub query should be avoided since it may impact the performance in case of large data.
Alternative are
1) Iterate the cursor and insert one by one
2) User of Distinct clause of SQL
3) User Correlated Subquery in case you need to use operators like >, = etc.
4) Bulk SQL - Support by databases like Oracle

Regards,