Subquery returned more than 1 value

Can someone help me solve the error of my query?

UPDATE [dbo].[fin_PaymentSchedule_Master]
SET
ActualPaymentDate_ColS = GETDATE()
,[PaymentVoucher_ColT] = 'PV-200780'
,pyLAK_ColU =
(
CASE
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'LAK'
THEN
(SELECT SUM(AmountPaid + Vat) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'THB'
THEN 0
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'USD'
THEN 0
END
)
,pyTHB_ColV =
(
CASE
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'THB'
THEN
(SELECT SUM(CurAmtPaid) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'LAK'
THEN 0
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'USD'
THEN 0
END
)
,pyUSD_ColW =
(
CASE
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'USD'
THEN
(SELECT SUM(CurAmtPaid) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'LAK'
THEN 0
WHEN
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC) = 'THB'
THEN 0
END
)
,pyTotalLAK_ColX = (SELECT SUM(AmountPaid + Vat) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHERE AP_Number_ColA = 'AP19-3131'

Error says:

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

The statement has been terminated. The code snipe above is only part of the sql transaction. thank you in advance.

UPDATE [dbo].[fin_PaymentSchedule_Master]
SET
ActualPaymentDate_ColS = GETDATE()
,[PaymentVoucher_ColT] = 'PV-200780'
,pyLAK_ColU =
(
CASE
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC)
WHEN 'LAK'
THEN (SELECT SUM(AmountPaid + Vat) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHEN'THB' THEN 0
WHEN 'USD' THEN 0
ELSE -1
END
)
,pyTHB_ColV =
(
CASE
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC)
WHEN 'THB' THEN
(SELECT SUM(CurAmtPaid) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHEN 'LAK' THEN 0
WHEN 'USD' THEN 0
ELSE -1
END
)
,pyUSD_ColW =
(
CASE
(SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC)
WHEN 'USD' THEN
(SELECT SUM(CurAmtPaid) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHEN'LAK' THEN 0
WHEN 'THB' THEN 0
ELSE -1
END
)
,pyTotalLAK_ColX = (SELECT SUM(AmountPaid + Vat) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
WHERE AP_Number_ColA = 'AP19-3131'

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Varun Setia6y ago
1. UPDATE [dbo].[fin_PaymentSchedule_Master]
2. SET
3. ActualPaymentDate_ColS = GETDATE()
4. ,[PaymentVoucher_ColT] = 'PV-200780'
5. ,pyLAK_ColU =
6. (
7. CASE
8. (SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC)
9. WHEN 'LAK'
10. THEN (SELECT SUM(AmountPaid + Vat) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
11. WHEN'THB' THEN 0
12. WHEN 'USD' THEN 0
13. ELSE -1
14. END
15. )
16. ,pyTHB_ColV =
17. (
18. CASE
19. (SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC)
20. WHEN 'THB' THEN
21. (SELECT SUM(CurAmtPaid) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
22. WHEN 'LAK' THEN 0
23. WHEN 'USD' THEN 0
24. ELSE -1
25. END
26. )
27. ,pyUSD_ColW =
28. (
29. CASE
30. (SELECT TOP(1) CurrencyPayment FROM fin_PaymentVoucher_Header WHERE PV_No = 'PV-200780' ORDER BY Id DESC)
31. WHEN 'USD' THEN
32. (SELECT SUM(CurAmtPaid) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
33. WHEN'LAK' THEN 0
34. WHEN 'THB' THEN 0
35. ELSE -1
36. END
37. )
38. ,pyTotalLAK_ColX = (SELECT SUM(AmountPaid + Vat) FROM fin_PaymentVoucher_Details WHERE AP_Number = 'AP19-3131')
39. WHERE AP_Number_ColA = 'AP19-3131'
This is simplified query with else as addon. Please try this