asked
Subquery returned more than 1 value. This is not permitted when the subquery follows
SELECT A.VMPRNO , A.VMMTNO , A.VMCNQT , (B.MMPUPR) + (CASE WHEN A.VMMTNO LIKE 'FOR%' THEN (SELECT (CASE WHEN D.PMMTNO LIKE 'FOR%' THEN (SELECT SUM (W.MMPUPR * Q.PMCNQT) / SUM(Q.PMCNQT) FROM MVXJDTAP.MVXJDTA.MPDMAT Q WITH(NOLOCK) INNER JOIN MVXJDTAP.MVXJDTA.MITMAS W WITH(NOLOCK) ON W.MMITNO = Q.PMMTNO WHERE Q.PMCONO = 605 AND W.MMCONO = 605 AND Q.PMPRNO = D.PMMTNO) ELSE SUM (C.MMPUPR * D.PMCNQT) / SUM(D.PMCNQT) END) FROM MVXJDTAP.MVXJDTA.MPDMAT D WITH(NOLOCK) INNER JOIN MVXJDTAP.MVXJDTA.MITMAS C WITH(NOLOCK) ON C.MMITNO = D.PMMTNO WHERE D.PMCONO = 605 AND C.MMCONO = 605 AND D.PMPRNO = A.VMMTNO GROUP BY D.PMMTNO) ELSE 0 END ) , A.VMMSEQ , VHMFNO AS 'OR NO' , VHSCHN AS 'PLAN' , 0 AS 'OR QT', 0 AS 'L/K', VHTXT1 , CAST (VHTXT2 AS int) AS 'TXT2' , A.VMREQT FROM (MVXJDTAP.MVXJDTA.MWOMAT A INNER JOIN MVXJDTAP.MVXJDTA.MWOHED ON VHMFNO = VMMFNO) INNER JOIN MVXJDTAP.MVXJDTA.MITMAS B ON VMMTNO = MMITNO WHERE VMCONO = 605 AND VMMFNO = 1001777 AND VHMFNO = 1001777 AND B.MMCONO = 605 AND A.VMPRNO = 'FOR4426' AND VHSLDT BETWEEN '20140101' AND '20141231'
What happens is that one of your subqueries is returning more than one value. I would try taking each subquery and running it on its own to see what's causing the problem, gradually working your way out of the nested levels of subquery. Are there values that "work" for this query? Chances are either your data isn't structured as you expect, or there's some duff data somewhere.
15 People are following this question.
