Subquery returned more than 1 value

Question

0.00/5 (No votes)

See more:

Hi All,

I have written the query like this::

SQL

select
B.PaymentId
,B.PayDate
--,D.FirstName+' '+D.LastName as PayFrom
,case when (B.PayFrom) is Null
 Then
  (select G.E_FName+ ' '+G.E_LName from LakesideLabSQL.dbo.tblGuardian G join LakesideLabSQL.dbo.tblUserPayment
   H on G.GuardianId=H.GuardianId where H.PayDate>'2013-04-02' and H.PayDate<DATEADD(day,1,'2013-04-2'))
    when (B.GuardianId) IS Null

    Then
    (select I.FirstName+' '+I.LastName from  LakesideLabSQL.dbo.tblUserPayment K join  LakesideLabSQL.dbo.tblUser I
      on K.PayFrom=I.UserId  where K.PayDate>'2013-04-02' and K.PayDate<DATEADD(day,1,'2013-04-2'))
    End
,B.MonerisMsg
,B.GuardianId
,B.RegId
,B.TransId
,B.PayAmount as Amount
,B.Status as PayStatus
,Null as CardAuthorizationCode

,B.CnfCode as Event
from LakesideLabSQL.dbo.tblUserPayment B
join LakesideLabSQL.dbo.tblUser D on D.UserId=B.PayFrom
 where B.PayDate>'2013-04-02' and B.PayDate<DATEADD(day,1,'2013-04-2')

But its throwing the error for me in Case part.The error is "subquery is returned more than 1 value.

VB

This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can you please tell me the solution how to make this work?

Posted 2-Apr-13 8:03am

priya9826

Add a Solution

Comments

ZurdoDev 2-Apr-13 14:20pm

The error tells you what is happening. Do you understand the error? It's hard to tell you how to fix this because I don't understand, in English, what you are trying to do.

priya9826 2-Apr-13 14:29pm

I am trying to get the name from the tbluser and tblGaurdian based on the userid and gaurdianId values from tbl userpayment.In tbluserpayment if userid value is present then gaurdianId value shows as null and if gaurdian id value is present then userid values is null.I need to get userid name from tbluser if gaurdianId value is null as vice versa

2 solutions

Solution 2

You can't use joins like this:

SQL

from LakesideLabSQL.dbo.tblUserPayment B JOIN LakesideLabSQL.dbo.tblUser D ON D.UserId=B.PayFrom

because UserID probably returns numeric values and PayFrom returns datetime values.

In JOIN statement you need to define fields with the same data types.

SQL

from LakesideLabSQL.dbo.tblUserPayment B JOIN LakesideLabSQL.dbo.tblUser D ON D.UserId=B.UserID

More:
http://www.w3schools.com/sql/sql_join.asp[^]
http://msdn.microsoft.com/en-us/library/aa196318%28v=sql.80%29.aspx[^]
http://msdn.microsoft.com/en-us/library/ms173815.aspx[^]

Posted 2-Apr-13 8:42am

Maciej Los

Updated 2-Apr-13 8:43am

v2

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)

Mike Meinz · Accepted Answer · 2013-04-02T08:32:00

The easy solution is to change all of the SELECT statements within the CASE statement to SELECT TOP 1. That, however, may not be the correct solution. It could be that the CASE statement is the wrong thing to use for your business requirement. You should study the data and determine if retrieving only one row for each clause of the CASE statement is indeed the correct solution.