Sub Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sub Query

Author

Topic

addytoe
Starting Member

5 Posts

Posted - 2009-11-15 : 22:11:35

I have tried using the sub query but it keeps displaying errors to me,

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

I did research and most forums say either use IN or NOT IN but it still do not work in my case...

SELECT DISTINCT req.RequestNo,
ISNULL((req.RequestAmt - pre.PaymentAmt), req.RequestAmt) AS Outstanding
FROM Trn_RequestPayment req
LEFT JOIN Trn_PrePayment pre
ON pre.RequestCompanyID = req.RequestCompanyID
AND req.RequestNo = pre.RequestNo --> This works totally fine

Sample data output of 1st Query
REQ091000002 405451.23
REQ091000003 10000.00
REQ091000004 300000.00
REQ091000004 400000.00
REQ091000006 22458.232
REQ091000008 102325.42
REQ091000010 4341.23
REQ091000012 34561.87
REQ091000013 265157.00
REQ091000014 100000.00
REQ091000015 9823.00
REQ091000016 4204.00
REQ091000017 12351.00
REQ091000018 20000.00
REQ091000019 2834.00
REQ091000021 3342.00
REQ091000022 54234.00
REQ091000023 100000.00
REQ091000024 200000.00
REQ091000025 2342.00
REQ091000026 2342.00
REQ091100001 1780401.00
REQ091100002 5202000.00
REQ091100005 10000000.00
REQ091100006 2000000.00
REQ091100010 542000.00
REQ091100011 8123989.00
REQ091100012 274191.29
REQ091100013 72348.23
REQ091100015 3457.22
REQ091100017 7624.23
REQ091100018 83765.12
REQ091100019 7624.23
REQ091100020 83765.12

(SELECT PaymentAmt AS [Approve Amount] FROM Trn_PrePayment WHERE IsApproved IN (1)) --> 2nd Query

Sample data output of 2nd Query
223423.00
244324.00
15434.00
542812.00
220000.00
234843.00
300000.00

Combining both queries together :
SELECT DISTINCT req.RequestNo,
ISNULL((req.RequestAmt - pre.PaymentAmt), req.RequestAmt) AS Outstanding,
(SELECT PaymentAmt AS [Approve Amount] FROM Trn_PrePayment WHERE IsApproved IN (1))
FROM Trn_RequestPayment req
LEFT JOIN Trn_PrePayment pre
ON pre.RequestCompanyID = req.RequestCompanyID
AND req.RequestNo = pre.RequestNo

Error displays... Am I going about the wrong direction?
I'm using SQL 2005. Thank you for your time and patience

prakum
Starting Member

16 Posts

Posted - 2009-11-15 : 23:17:15

May i know what are the fields presents in Trn_RequestPayment and Trn_PrePayment ...Over that writing the second query inside first does not produce any meaning k...provide me with the field details of two tables

Praveen Kumar

addytoe
Starting Member

5 Posts

Posted - 2009-11-16 : 00:43:30

Hey prakum,

The 1st Query or the Trn_RequestPayment is where a payee would send an invoice to the Payer and in this case, it has got a Request No, varchar(50), which is an incremental value and the other field is the Outstanding Amount, which is taking the Payee requested Amount and minus off the PaymentAmt made by the Payer.

The 2nd Query or the Trn_PrePayment has got PaymentAmt, money and it is where a payer makes a payment to the invoice but it must be verified or approved first before it goes through.

Thus, the field that I want to combine along with the 1st Query to display details of Request No, Outstanding and PaymentAmt. The condition of the PaymentAmt is that I want to show PaymentAmt that has been approved, IsApproved, bit.

If I am missing out any details, please let me know as I wish to solve this issue.. Thanks for the time

prakum
Starting Member

16 Posts

Posted - 2009-11-16 : 04:50:57

hey addytoe

As far as i have understood your problem is that you want to display the ReqNo,Outstanding amount and the approved amount...so here is my solution just check out with this if any modification is question reply....

Condition is that if that prePayment is not approved it will not show the record..ok

SELECT req.RequestNo,
ISNULL((req.RequestAmt - pre.PaymentAmt), req.RequestAmt) AS Outstanding,pre.PaymentAmt AS [Approve Amount]
FROM Trn_RequestPayment req
LEFT JOIN Trn_PrePayment pre
ON pre.RequestCompanyID = req.RequestCompanyID
AND req.RequestNo = pre.RequestNo
AND pre.IsApproved=1

Praveen Kumar

addytoe
Starting Member

5 Posts

Posted - 2009-11-16 : 05:30:56

Thanks alot Praveen !!!

It's working and displaying perfectly

You made it look so simple and comparing to mine, I must have made something simple into more complex than it was supposed to be.

Thanks again ... I definitely something from this

prakum
Starting Member

16 Posts

Posted - 2009-11-16 : 23:53:59

Ya its ok addy....

Praveen Kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources