Click here to Skip to main content
13,086,120 members (84,057 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,

I have written the query like this::
--,D.FirstName+' '+D.LastName as PayFrom
,case when (B.PayFrom) is Null
  (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
    (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'))
,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.
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
ryanb31 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
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

You can't use joins like this:
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.
from LakesideLabSQL.dbo.tblUserPayment B JOIN LakesideLabSQL.dbo.tblUser D ON D.UserId=B.UserID


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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.170813.1 | Last Updated 2 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100