Hi.
This query for just one client, returns me a comission map working just fine. But form more than one client, for sometimes it works, and others don't.
When the query don't work, the message is:
"Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
I don't understand why, because my subquery's are selecting just one value. And i can't see where's the erros.
My query:
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.SELLER, 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 < '20150401') 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 < '20150401') 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 >= '20150401' AND OITR.ReconDate <= '20150731'
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 >= '20150401' AND OITR.ReconDate <= '20150731'
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
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 = 284) as 'Brand',
(select top 1 U_ARGNS_Coll from OITM
INNER JOIN inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = 284) 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 inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = 284)
and U_Collection =
(select top 1 U_ARGNS_Coll from OITM inner join inv1 on inv1.itemcode = oitm.ItemCode
where inv1.docentry = 284) and U_Seller in ('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 >='20150401' AND OT.RECONDATE <= '20150731' AND ir.SRCOBJTYP <> 203 /* ADC */ and ir.srcobjtyp = 13
AND OV.CARDCODE IN ('C00000','C00001','C00002','C00003','C00004','C00005','C00006') AND OV.CARDCODE LIKE 'C%' AND IR.ISCREDIT = 'D'
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.SELLER, T100.CARDCODE, T100.CARDNAME,
T100.DOCCUR, T100.Country, T100.Brand, T100.Collection, T100.Comission
)T200
Thanks,
Anita