SQL Server Error: Subquery returned more than 1 value

Last Reply one year ago By dharmendr

Posted one year ago

why im seeing this error..???

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

            DECLARE @AdmissionNo VARCHAR(10)           
            ;WITH cteStudentMarksDetails
                AS (
                select ts.AdmissionNo
                    ,TestDate
                    ,SubjectName
                    ,MaxMarks
                    ,Marks
                    ,Round((Marks) * 100/ (MaxMarks),1) as SubjectPercentage
                    ,DENSE_RANK() over(partition BY (SELECT MAX(Marks) FROM tblTestSystem TSS Where Tss.SubjectID = ts.SubjectID AND Tss.ClassID = ts.ClassID)  order by ((Round((SELECT Marks FROM tblTestSystem TSS Where Tss.AdmissionNo = ts.AdmissionNo AND Tss.SubjectID = ts.SubjectID AND Tss.ClassID = ts.ClassID) * 100 / (SELECT MAX(Marks) FROM tblTestSystem TSS Where Tss.SubjectID = ts.SubjectID AND Tss.ClassID = ts.ClassID),1) )) DESC) AS SubjectPosition
                    ,CASE WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 80 THEN 'A+'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 70 THEN 'A'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 60 THEN 'B'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 50 THEN 'C'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 40 THEN 'D'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 33 THEN 'E'
                          ELSE 'FAIL' END AS Grade,
                          CASE WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 80 THEN 'OutStanding'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 70 THEN 'Excellent'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 60 THEN 'Very Good'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 50 THEN 'Good'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 40 THEN 'Satisfactory'
                          WHEN  Round((Marks) * 100/ (MaxMarks),1) >= 33 THEN 'Work Hard'
                          ELSE 'FAIL' END AS Remarks   
                    ,(Round((SELECT SUM(Marks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo) * 100/ (SELECT SUM(MaxMarks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo),1) ) TotalPercentage
                    ,DENSE_RANK() OVER(ORDER BY ((ROUND((SELECT SUM(Marks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo) * 100 / (SELECT SUM(MaxMarks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo),1) )) DESC) AS ClassPosition
                FROM tblTestSystem AS ts
                INNER JOIN tblStdReg AS sr ON ts.AdmissionNo=sr.AdmissionNo
                INNER JOIN tblDefClass AS dc ON ts.ClassID=dc.ClassID
                INNER JOIN tblDefSection AS ds ON ts.SectionID=ds.SectionID
                inner join tblAssignSubjects as si on si.ASID=ts.SubjectID 
				inner join tblDefSubject as des on si.SubjectID=des.SubjectID
                
                )
 
                SELECT * FROM cteStudentMarksDetails
Posted one year ago

Its not possible to check your query. It requires complete database structure and data which we don't have and not possible to create for you to check.

Please check your sub query which returns multiple record and change it to get single record based on your requirement.