Sql subquery returning more than one value

Question

0.00/5 (No votes)

See more:

Hi everyone

Could someone maybe help me i am trying to figure out my problem. Im getting the below error when running the below stored proc.

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

Quote:
USE [warranty]
GO
/****** Object: StoredProcedure [dbo].[spProductDetailsInsert] Script Date: 01/22/2015 11:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spProductDetailsInsert]

@UserId int

AS

BEGIN

declare @IdentityOutput table ( ID int )

Begin
INSERT INTO dbo.tblProductDetails(DateLoaded, Order_Number,Shipping_Date,SKU,Product_Description,Product_Supplier,Product_Group,Serial_Number,Client,IMEI,Warranty_Period,UserId)output inserted.ID into @IdentityOutput

SELECT GETDATE(), Order_Number,Shipping_Date,SKU,Product_Description,
(select id from tblProductSupplier where Supplier =sm.Product_Supplier) as [Product_Supplier],(select Id from tblProduct_Group where [Group] = sm.Product_Group) as [Product_Group],Serial_Number,
(select ClientID from tblClient where Client_name = sm.Client) as [Client],IMEI,
(select Id from tblWarrantyPeriod where Period = sm.Warranty_Period) as [Warranty_Period],@UserId
FROM dbo.tblTmpProductDetails sm
where sm.IsError is null

insert INTO tblCustomer(ProductId,FullName,Cell,Email,Landline,UserId)
select (select ID from @IdentityOutput),CustomerName,CustomerCell,CustomerEmail,CustomerLandLine,@UserId
from tblTmpProductDetails
end

delete from tblTmpProductDetails

END

Posted 21-Jan-15 22:42pm

mrDivan

Add a Solution

Comments

RAHUL(10217975) 22-Jan-15 4:53am

Your subquery should return 1 value .you can't return multiple rows in subquery.

RAHUL(10217975) 22-Jan-15 4:56am

Use Joins instead Subquery It will work fine.

deepakdynamite 22-Jan-15 4:58am

Or Write Select top 1 in subquery

2 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)

Wendelius · Answer 1 · 2015-01-21T22:56:00

You have several subqueries that can cause this problem. For example

SQL

SELECT GETDATE(), Order_Number,Shipping_Date,SKU,Product_Description,
 (select id from tblProductSupplier where Supplier =sm.Product_Supplier) as [Product_Supplier],...

if the condition where Supplier =sm.Product_Supplier produces more than 1 row this error is generated.

So go through all the subqueries and check that they are correctly correlated to the outer query.

Also, use table aliases in all conditions inn order to avoid ambiquity in conditions.

Thava Rajan · Answer 2 · 2015-01-21T23:21:00

obviously the error says one of your subquery is return more than one result

Seems one of your table does not have a primary key
so you have do the following steps

just check the following queries

SQL

select Client_name, Count(*) from tblClient 
Group by Client_name 
Having count(*)>1

select Supplier, count (*) from tblProductSupplier 
Group by Supplier 
Having count(*)>1

select [Group], count (*)  from tblProduct_Group 
Group by  [Group]
Having count(*)>1

select Period , count (*) from tblWarrantyPeriod 
Group by  Period 
Having count(*)>1

if any of the above query return results then you need to check the data in that table
(keep it in mind i have just give you a way to solve this issue)
i feel the first query return results in that case try to recreate the query with clientid instead of client name in your query
like this

(select ClientID from tblClient where ClientID = sm.Clientid)

Hope you understand ;)

Sql subquery returning more than one value

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0