Solve this problem......"Subquery returned more than 1 value"

[Former Member]

Hi Experts,

Tell me the solution for this problem......

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

This is the query...

SELECT DISTINCT ORPC.DocEntry, ORPC.DocNum AS 'Debit Note No.', ORPC.CardName AS 'Name', ORPC.CardCode AS 'code', ORPC.NumAtCard AS 'Supplier Ref.',

OCTG.PymntGroup AS 'Payment Terms', OSHP.TrnspName AS 'Shipping type', RPC1.Dscription AS 'Description of Goods', ORPC.Address,

RPC1.Quantity, RPC1.Price AS 'Rate', RPC1.LineTotal AS 'Amount', ORPC.DocDate,

(SELECT DISTINCT TaxRate FROM RPC4

WHERE (staType = - 90) AND (StaCode = 'BED@10') AND (DocEntry = RPC1.DocEntry)) AS 'Basic Excise Duty BED@10 %',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_8

WHERE (staType = - 60) AND (DocEntry = RPC1.DocEntry)) AS 'Education Cess @2%',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_7

WHERE (staType = - 55) AND (DocEntry = RPC1.DocEntry)) AS 'Secondary Education Cess @1%',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_6

WHERE (staType = 4) AND (StaCode = 'CST2') AND (DocEntry = RPC1.DocEntry)) AS 'Central Sales Tax(CST)@2%',

(SELECT DISTINCT TaxRate FROM PCH4

WHERE (staType = 4) AND (StaCode = 'CST12.5') AND (DocEntry = RPC1.DocEntry)) AS 'Central Sales Tax(CST)@12.5%',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_5

WHERE (staType = 1) AND (StaCode = 'VAT4') AND (DocEntry = RPC1.DocEntry)) AS 'Input VAT@5%',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_4

WHERE (staType = 1) AND (TaxRate = 12.500000) AND (StaCode = 'VAT12.5') AND (StaCode = 'VAT12.5') AND (DocEntry = RPC1.DocEntry)) AS 'Input VAT@12.5%',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_3

WHERE (staType = 18) AND (StaCode = 'Add2') AND (DocEntry = RPC1.DocEntry)) AS 'Addtional Tax@1%',

(SELECT DISTINCT TaxRate FROM RPC4 AS RPC4_3

WHERE (staType = 18) AND (StaCode = 'Add1S') AND (DocEntry = RPC1.DocEntry)) AS 'Addtional Tax@1%',

(SELECT DISTINCT TaxSum FROM RPC4

WHERE (staType = - 90) AND (StaCode = 'BED@10') AND (DocEntry = RPC1.DocEntry)) AS 'BedAmount@10%',

(SELECT DISTINCT TaxSum FROM RPC4

WHERE (staType = - 60) AND (DocEntry = RPC1.DocEntry)) AS 'EcessAmount@2%',

(SELECT DISTINCT TaxSum FROM RPC4

WHERE (staType = - 55) AND (DocEntry = RPC1.DocEntry)) AS 'SecCessAmount@1%',

(SELECT DISTINCT TaxSum FROM RPC4

WHERE (staType = 4) AND (StaCode = 'CST2') AND (DocEntry = RPC1.DocEntry)) AS 'CSTAmount@2%',

(SELECT DISTINCT TaxSum FROM RPC4 AS RPC4_5

WHERE (staType = 1) AND (StaCode = 'VAT4') AND (DocEntry = RPC1.DocEntry)) AS 'VATAmount@5%',

(SELECT DISTINCT TaxSum FROM RPC4 AS RPC4_4

WHERE (staType = 1) AND (TaxRate = 12.500000) AND (StaCode = 'VAT12.5') AND (DocEntry = RPC1.DocEntry)) AS 'VATAmount@12.5%',

(SELECT DISTINCT TaxSum FROM RPC4 AS RPC4_3

WHERE (staType = 18) AND (StaCode = 'Add2') AND (DocEntry = RPC1.DocEntry)) AS 'AddTaxAmount@1%(for VAT@4)',

(SELECT DISTINCT TaxSum FROM RPC4 AS RPC4_3

WHERE (StcCode = 'VAT12.5') AND (staType = 18) AND (StaCode = 'Add1S') AND (DocEntry = RPC1.DocEntry)) AS 'AddTaxAmount@1%(forVAT@12.5)',

(SELECT DISTINCT TaxSum FROM PCH4

WHERE (StcCode = 'B10C125P') AND (TaxRate = 12.500000) AND (staType = 4) AND (StaCode = 'CST12.5') AND (DocEntry = RPC1.DocEntry)) AS 'CSTAmount@12.5%'

FROM ORPC INNER JOIN

RPC1 ON ORPC.DocEntry = RPC1.DocEntry INNER JOIN

OSHP ON ORPC.TrnspCode = OSHP.TrnspCode INNER JOIN

OCTG ON ORPC.GroupNum = OCTG.GroupNum

WHERE (ORPC.CardCode = 'V00522') AND (ORPC.DocNum = '9220005')

Regards

Rahul

[Former Member · Accepted solution]

Hi Rahul......

please try this and make necessary changes wherever required.....

SELECT SUBSTRING((CONVERT(VARCHAR(11),T0.DOCDATE,106)),4,11), T0.DocEntry, T0.[DocNum], T0.[DocDate], 
T0.Cardcode, T0.CardName, T0.NumAtCard,T2.PymntGroup AS 'Payment Terms', T3.TrnspName AS 'Shipping type',
T1.Dscription, T1.[Quantity] 'Quantity', T1.[LineTotal] 'Amount',
(isnull((SELECT SUM((case when upper(t4.STAType)=-90 then T4.TaxSum else 0 end))
	 FROM RPC4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum=T4.LineNum),0)) 'Cenvat 10%', 
(isnull((SELECT SUM((case when upper(t4.STAType) =-60 then T4.TaxSum else 0 end))
	 FROM RPC4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum=T4.LineNum),0)) 'Edu. Cess 2%',
	(isnull((SELECT SUM((case when upper(t4.STAType) =-55 then T4.TaxSum else 0 end))
	 FROM RPC4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum=T4.LineNum),0)) 'S&H Cess 1%', 
(isnull((SELECT SUM((case when upper(t4.STAType) =1 and t4.TaxRate=4 then T4.TaxSum else 0 end))
	 FROM RPC4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum=T4.LineNum ),0)) 'VAT4%',
(isnull((SELECT SUM((case when upper(t4.STAType)=18 and t4.TaxRate=1 then T4.TaxSum else 0 end))
	 FROM RPC4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum=T4.LineNum),0)) 'VAT1%'
 FROM ORPC T0  INNER JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry Inner Join OCTG T2 On T0.GroupNum = T2.GroupNum
INNER JOIN OSHP T3 ON t0.TrnspCode = t3.TrnspCode
Order By T0.[DocDate]

Hope this will help you.....

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul,

Thanks for the solution, it really worked well.

Regards

Rahul

Edited by: 118Rahul on Sep 19, 2011 5:37 PM

[Former Member]

Hi,

When using subquery the query result must be 1 value only. Most of the query uses SUM, MIN, MAX or TOP

to have 1 value only.

Run each of your subquery separately , the result of each subquery must 1 value.

Thanks.

Clint