SQL Subquery with more than 1 value

Transact-SQL
1

This query tells me:

"Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.".

My query:

-- FATURAS

SELECT *, (CASE WHEN T200.DOCTOTAL-T200.RECONSUM-T200.[PAID BEFORE]-T200.PAGONCPERIODO = 0 THEN 'TRUE' ELSE 'FALSE' END) AS 'INCLUDE?'
FROM
(
SELECT T100.DOCENTRY, T100.DOCNUM, T100.INVTYPE, T100.[DOC. TYPE], T100.DOCCUR, T100.CARDCODE, T100.CARDNAME, T100.Country
, (SELECT DOCTOTAL FROM OINV WHERE DOCNUM = T100.DOCNUM) AS DOCTOTAL
, (SELECT DOCTOTALFC FROM OINV WHERE DOCNUM = T100.DOCNUM) AS DOCTOTALFC
, sum(T100.RECOMSUM) AS RECONSUM, SUM(T100.RECOMSUMFC) AS RECONSUMFC
, (SELECT COALESCE (SUM(IR.RECONSUM),0) FROM OITR OI
INNER JOIN ITR1 IR ON IR.RECONNUM = OI.RECONNUM 
WHERE IR.SrcObjAbs = T100.DOCENTRY AND OI.RECONDATE < '20150601') as 'PAID BEFORE'
, (SELECT COALESCE (SUM(IR.RECONSUMFC),0) FROM OITR OI
INNER JOIN ITR1 IR ON IR.RECONNUM = OI.RECONNUM 
WHERE IR.SrcObjAbs = T100.DOCENTRY AND OI.RECONDATE < '20150601') as 'PAID BEFORE FC',
(SELECT COALESCE (SUM(ReconSum),0) FROM ITR1 INNER JOIN OITR ON OITR.ReconNum = ITR1.ReconNum 
WHERE ITR1.RECONNUM = (SELECT TOP 1 RECONNUM FROM ITR1 WHERE SrcObjAbs = T100.DOCENTRY) 
AND OITR.ReconDate >= '20150601' AND OITR.ReconDate <= '20150630'
AND IsCredit = 'C'  AND SrcObjTyp = 14) AS PAGONCPERIODO,
(SELECT COALESCE (SUM(ReconSumFC),0) FROM ITR1 INNER JOIN OITR ON OITR.ReconNum = ITR1.ReconNum 
WHERE ITR1.RECONNUM = (SELECT TOP 1 RECONNUM FROM ITR1 WHERE SrcObjAbs = T100.DOCENTRY) 
AND OITR.ReconDate >= '20150601' AND OITR.ReconDate <= '20150630'
AND IsCredit = 'C'  AND SrcObjTyp = 14) AS PAGONCPERIODOFC
, T100.Brand, T100.Collection, T100.Comission,
(SELECT DOCTOTAL-VATSUM-TotalExpns FROM OINV WHERE DOCNUM = T100.DOCNUM) AS VALORLIQ,
(SELECT DOCTOTALFC-VATSUMFC-TotalExpFC FROM OINV WHERE DOCNUM = T100.DOCNUM) AS VALORLIQFC,
(SELECT U_ARGNS_BRAND FROM OITM WHERE T100.BRAND = OITM.U_ARGNS_BRAND) AS ROYALTY
FROM
(
SELECT
--IR.ReconNum,
OV.DocEntry AS 'DOCENTRY',
OV.DOCNUM AS 'DOCNUM', 
IR.SRCOBJTYP AS 'INVTYPE', 
CASE IR.SRCOBJTYP WHEN '13' THEN 'INVOICE' ELSE 'CREDIT NOTE' END AS 'DOC. TYPE',
S.SLPNAME AS 'SELLER', 
--OT.RECONDATE AS 'DOC. DATE',
OV.CARDCODE AS 'CARDCODE', 
OV.CARDNAME AS 'CARDNAME', 
OT.ReconCurr as 'DOCCUR', 
OC.Country, 
IR.ReconSum AS 'RECOMSUM',
IR.ReconSumFC AS 'RECOMSUMFC',
(select top 1 U_ARGNS_Brand from OITM 
INNER JOIN inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) as 'Brand',
(select top 1 U_ARGNS_Coll from OITM 
INNER JOIN inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) as 'Collection',
(Select L.U_Comission 
from [@SBSS_COMISS_VEND_I] L Inner Join [@SBSS_COMISS_ROYAL_H] C on L.DocEntry = C.DocEntry
Where U_Brand = (select top 1 U_ARGNS_Brand from OITM inner join inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) and U_Collection = 
(select top 1 U_ARGNS_Coll from OITM inner join inv1 on inv1.itemcode = oitm.ItemCode 
where inv1.docentry = OV.DOCENTRY) AND C.U_ROYALTY = ('MARKUS LUPFER')) 
--and U_Seller = 'MATILDA') 
as 'Comission'

FROM ITR1 IR 
INNER JOIN OITR OT ON OT.RECONNUM = IR.RECONNUM
INNER JOIN OINV OV ON OV.DOCENTRY = IR.SRCOBJABS
INNER JOIN OSLP S ON S.SLPCODE = OV.SLPCODE
INNER JOIN OCRD OC ON OC.CARDCODE = OV.CARDCODE

WHERE 
OT.RECONDATE >= '20150601' AND OT.RECONDATE <= '20150630' AND ir.SRCOBJTYP <> 203 /* ADC */ and ir.srcobjtyp = 13 
AND OV.CARDCODE IN ('C00522') AND IR.ISCREDIT = 'D' AND ((select top 1 U_ARGNS_Brand from OITM 
INNER JOIN inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) IN ('MARKUS LUPFER'))
--AND S.SLPNAME IN ('MATILDA')
AND (SELECT SrcObjTyp FROM ITR1 CRED WHERE CRED.ReconNum = ir.ReconNum AND IsCredit='C') <> 14 -- importante
) T100
GROUP BY T100.DOCENTRY,T100.DOCNUM, T100.INVTYPE, T100.[DOC. TYPE], T100.CARDCODE, T100.CARDNAME,
T100.DOCCUR, T100.Country, T100.Brand, T100.Collection, T100.Comission
)T200
--WHERE T200.DOCTOTAL-T200.RECONSUM-PAGONCPERIODO = 0


UNION ALL

SELECT DOCENTRY, DOCNUM, 14 AS INVTYPE, 'CREDIT NOTE' AS [DOC. TYPE], DocCur, (SELECT SLPNAME FROM OSLP WHERE OSLP.SLPCODE = ORIN.SlpCode) AS SELLER
, CardCode, CardName
, (SELECT COUNTRY FROM OCRD WHERE CARDCODE = ORIN.CardCode), -DocTotal, -DocTotalFC, -DocTotal AS RECONSUM, -DocTotalFC AS RECONSUMFC, 0 AS 'PAID BEFORE'
, 0 AS 'PAID BEFORE FC', 0 AS PAGONCPERIODO, 0 AS PAGONCPERIODOFC,
(select top 1 U_ARGNS_Brand from OITM 
INNER JOIN RIN1 on RIN1.itemcode = oitm.ItemCode where RIN1.docentry = ORIN.DOCENTRY) as 'Brand',
(select top 1 U_ARGNS_Coll from OITM 
INNER JOIN RIN1 on RIN1.itemcode = oitm.ItemCode where RIN1.docentry = ORIN.DOCENTRY) as 'Collection',
(Select L.U_Comission 
from [@SBSS_COMISS_VEND_I] L Inner Join [@SBSS_COMISS_VEND_H] C on L.DocEntry = C.DocEntry
Where U_Brand = (select top 1 U_ARGNS_Brand from OITM inner join RIN1 on RIN1.itemcode = oitm.ItemCode where RIN1.docentry = ORIN.DOCENTRY) and U_Collection = 
(select top 1 U_ARGNS_Coll from OITM inner join RIN1 on RIN1.itemcode = oitm.ItemCode 
where RIN1.docentry = ORIN.DOCENTRY) and U_Seller = 'MATILDA') as 'Comission',
-(DOCTOTAL-VATSUM-TotalExpns) AS VALORLIQ,
-(DOCTOTALFC-VATSUMFC-TotalExpFC) AS VALORLIQFC
, 'TRUE' AS 'INCLUDE?'
FROM ORIN WHERE 
DOCDATE >= '20150601' AND DOCDATE <= '20150630' and ORIN.CARDCODE IN ('C00522')

Can someone see what is the subquery that's causing trouble?

Thanks,
Anita

2

Without doing some debugging, it is hard to tell which of your subqueries is causing the problem. You might double click on the error in SSMS, which will take you to the line in the code where SQL Server thinks the error is.

To help you debug, here is a simple example that shows the problem. The problem in this example is that for a given value of id in table #A, there is more than one value in table #B, which causes the subquery to return more than one row. That is not allowed.

CREATE TABLE #a(id INT);
CREATE TABLE #b(id INT);
INSERT INTO #a VALUES (1);
INSERT INTO #b VALUES (1),(1);

SELECT
	a.id,
	(SELECT b.id FROM #b b WHERE b.id = a.id)
FROM
	#a a;
	
DROP TABLE #a, #b;

A possible alternative, assuming that is what your requirement is as follows:

SELECT 
	a.id,
	b.id
FROM
	#a a
	INNER JOIN #b b ON a.id = b.id;

That, of course, will return two rows. That may not be what you want.