Post reply

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

  • November 6, 2009 at 3:56 am

    #142883

    Dear friends;

    Pls help this

    I run the qry in sqlserver 2005

    select max(smd.Yearpassed) [ExamSession],max(SMD.ASESSIONCODE),max(SMD.COURSEID),SM.REGISTERNO,max(SMD.GRADE),max(Cmas.CID),max(CMas.CNAME),max(SM.STUDNAME) [NAME]

    ,DM.DEGNAME + SPACE(1) +BM.Branchname [PROGRAMME NAME],max(SMD.ASESSIONCODE),dbo.GetStudentAttempt(SM.RegisterNo,SMD.CourseId,Yearpassed) as Attempt,

    MAX(gpc.sgpa),MAX(gpc.cgpa),MAX(gac.Gacid),MAX(cm.coursetype) from Studentmarkdetails smd

    inner join Studentmaster sm on sm.registerno=smd.registerno and sm.statusflag not in ('D')

    inner join TempgacidGeneration gac on gac.registerno=smd.registerno

    inner join coursemaster cm on cm.courseid=smd.courseid

    inner join collegemaster cmas on cmas.cid=substring(smd.registerno,7,3)

    inner join degreemaster dm on dm.degcode=substring(smd.registerno,3,2)

    inner join Branchmaster bm on bm.Bcode=substring(smd.registerno,3,4)

    inner join gradepointcalculation gpc on gpc.registerno=smd.registerno

    where smd.appstatus in('A','E') and smd.publishflag='Y'

    and smd.registerno='078002100023' and gac.gacid='A00201A09D59'-- and smd.Examscheduleid='19N07A'

    group by smd.Yearpassed,SMD.ASESSIONCODE,SM.REGISTERNO,SMD.GRADE,Cmas.CID,CMas.CNAME,SM.STUDNAME

    ,DM.DEGNAME + SPACE(1) +BM.Branchname,SMD.ASESSIONCODE,dbo.GetStudentAttempt(SM.RegisterNo,SMD.CourseId,Yearpassed),

    gpc.sgpa,gpc.cgpa,gac.Gacid,cm.coursetype

    To run the qry to throw the following error

    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.

    How to solve the problem ;

    I trace out the error is The two Duplication column of Yearofpassed in Two times

    Like

    Year of Passed

    Jan -09

    Jan -09

    How can i Sove the problem;

    Thanks & Regards;

    A.Faijurrahuman

  • November 6, 2009 at 4:48 am

    #1075819

    Can you confirm if the function dbo.GetStudentAttempt is scalar or table valued?

    If it is table valued function, there is a possibility that you get more than one row in the result due to which it will throw the mentioned exception

  • November 6, 2009 at 5:23 am

    #1075844

    Hi;

    It's is scalar function It's return only one row

  • November 6, 2009 at 5:28 am

    #1075846

    There may be an error in the function, can you post the code for that?

  • November 6, 2009 at 5:29 am

    #1075847

    Hi

    Since there is no sub-query in your posted statement there seem to be a sub-query within your scalar function which gets its value from a SELECT. Probably this returns more than one row.

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • November 6, 2009 at 7:00 am

    #1075881

    If possible can you provide the function definition (with just input and output values), so that we could confirm.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply