SQL subquery return more than 1 value. Help me solve with this Sql query

Question

0.00/5 (No votes)

See more:

this is my SQL query.

SQL

"select Unit from UnitConversion "+
               "where uocNo=(select uocNo from UnitConversionProduct "+
               "where UnitConversionProduct.ProductNo=@ProductNo)"

UnitConversion table has following columns
uocNo
Unit
Amount
Base

UnitConversionProduct table has following columns
uocNo
ProductNo

when i execute that query the following error appear

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

Posted 6-Apr-13 6:30am

deva936

Add a Solution

Comments

vinodkumarnie 6-Apr-13 13:11pm

It throws an error means there is mistake in your Syntax. Verify the datatype and then try..

3 solutions

Solution 1

When you use =,!=, <, <= , >, >= in where clause you can compare it(here uocNo) to only one value , but in your query it`s return more than one value.(Try to run this

SQL

select uocNo from UnitConversionProduct "+
               "where UnitConversionProduct.ProductNo=@ProductNo

you will get more than one value.) For solutions you have to ensure that subquery must return only one value. I think you must have multiple value for single ProductNo.

Posted 6-Apr-13 6:56am

Anil Limbani

Solution 3

You can use in subquery:
1) TOP()[^] statement

SQL

SELECT Unit
FROM UnitConversion
    WHERE uocNo=(SELECT TOP(1) uocNo
                 FROM UnitConversionProduct
                 WHERE UnitConversionProduct.ProductNo=@ProductNo)

or
2) DISTINCT[^] statement

SQL

SELECT Unit
FROM UnitConversion
    WHERE uocNo=(SELECT DISTINCT uocNo
                 FROM UnitConversionProduct
                 WHERE UnitConversionProduct.ProductNo=@ProductNo)

In most cases using TOP and/or DISTINCT should helps to return 1 record.

Posted 6-Apr-13 8:47am

Maciej Los

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)

RaisKazi · Accepted Answer · 2013-04-06T08:04:00

Error message you are getting is very well self explanatory. Its failing because your SubQuery returning more than one value. Try either of below two queries.

SQL

SELECT Unit FROM UnitConversion where uocNo in(select uocNo from UnitConversionProduct WHERE UnitConversionProduct.ProductNo=@ProductNo)

OR

SQL

SELECT Unit FROM UnitConversion UC INNER JOIN UnitConversionProduct UCP ON UC.uocNo = UCP.uocNo WHERE UCP.ProductNo = @ProductNo

I recommend you to use second query becuase Inner Join is faster in performance as compare to SubQuery.

Hope it helps!