Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi All,
 
I have written the query like this::
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.
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 9:03am
Comments
ryanb31 at 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 at 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
good
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.
  Permalink  
Rate this: bad
good
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
 
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[^]
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 275
1 Maciej Los 230
2 Sergey Alexandrovich Kryukov 185
3 Afzaal Ahmad Zeeshan 152
4 ProgramFOX 130
0 OriginalGriff 6,524
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,218
3 Manas Bhardwaj 4,657
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 2 Apr 2013
Copyright © CodeProject, 1999-2014
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