Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Question Unanswered: Error In query !!!

    hi !

    while executing the query

    Select *,(Select CourseGroupName from CourseGroupDetails where CourseGroupCode = A.CourseGroup) 'CourseGroupName',
    (Select ProgramName from ProgramDetails where ProgramCode IN(Select ProgramCode from CourseGroupDetails where
    CourseGroupCode = A.CourseGroup)) 'ProgramName',
    (Select CourseName from NRICourseDetails where CourseCode = A.CourseCode and CenterCode=A.CenterCode AND Year=(Select Max(Year) from NRICourseDetails where Year<=A.AdmissionYear)) 'CourseName' from NRIAdmissionDetails as A
    where IsDeleted=0 and RegisterNo='a6c2119701'

    Error 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.
    if anyone know pls reply me...!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The error is very well described in the message. Your subqueries cannot return more than one value, but they do.

    This is not the most elegant query I've ever seen either. What are you trying to do?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    10
    hi ! Thanks for your reply...

    this query is not written by me... am debugging the project ! while debugging find the error in the query but don't know what to do with. while searching in Google will know about the dbforums and i posted here ! is there any way to correct it...?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT A.*
         , CGD.CourseGroupName 
         , PD.ProgramName
         , N.CourseName 
      FROM NRIAdmissionDetails as A 
    INNER
      JOIN CourseGroupDetails AS CGD
        ON CGD.CourseGroupCode = A.CourseGroup
    INNER
      JOIN ProgramDetails AS PD
        ON PD.ProgramCode = CGD.ProgramCode
    INNER
      JOIN NRICourseDetails AS N
        ON N.CourseCode = A.CourseCode
       AND N.CenterCode = A.CenterCode 
       AND N.Year = (SELECT MAX(Year) 
                       FROM NRICourseDetails 
                      WHERE Year <= A.AdmissionYear )
    
     WHERE A.IsDeleted=0 
       AND A.RegisterNo = 'a6c2119701'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    try like this
    Select a.*,CourseGroupName,ProgramName,CourseName
    from NRIAdmissionDetails as A
    JOIN
    CourseGroupDetails cd ON cd.CourseGroupCode = A.CourseGroup
    JOIN
    ProgramDetails pd ON pd.CourseGroupCode = A.CourseGroup AND pd.ProgramCode = cd.ProgramCode
    JOIN
    NRICourseDetails N ON n.CourseCode = A.CourseCode and n.CenterCode=A.CenterCode
    AND n.Year=(Select Max(Year) from NRICourseDetails where Year<=A.AdmissionYear)
    WHERE a.IsDeleted=0 and a.RegisterNo='a6c2119701'

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mine's prettier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    10
    Hi ! r937...!

    thanks for your reply ! query send by you is working without any error but the same rows are repeated... is any way to control the rows to occur once..! in sql it didnt show any error but while running the error
    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
    is displayed

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are we still talking about SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2008
    Posts
    135
    use select distinct ...........
    to avoid duplicates

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bklr
    use select distinct ...........
    to avoid duplicates
    Given there is a SELECT *, I rather hope it does not
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2009
    Posts
    10
    Hi ! in the query

    SELECT A.*
    , CGD.CourseGroupName
    , PD.ProgramName
    , N.CourseName
    FROM NRIAdmissionDetails as A
    INNER
    JOIN CourseGroupDetails AS CGD
    ON CGD.CourseGroupCode = A.CourseGroup
    INNER
    JOIN ProgramDetails AS PD
    ON PD.ProgramCode = CGD.ProgramCode
    INNER
    JOIN NRICourseDetails AS N
    ON N.CourseCode = A.CourseCode
    AND N.CenterCode = A.CenterCode
    AND N.Year = (SELECT MAX(Year)
    FROM NRICourseDetails
    WHERE Year <= A.AdmissionYear )

    WHERE A.IsDeleted=0
    AND A.RegisterNo = 'a6c2119701'

    if i used Distinct in the place of * then the error is
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'Distinct'.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just throwing DISTINCT into a query usually does not solve the (underlying) problem, and often has no effect because something will be different on each row

    you will have to show us examples of query result rows which demonstrate the "duplicates"

    they are likely coming from the same place which produced multiple subquery rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2009
    Posts
    10
    hi r937 !

    now the error changed to

    Procedure or function sp_Select_NRIAdmissionDetails has too many arguments specified.

    not in the sqlserver the error is displayed in the page while running
    in sql server the row for the RegisterNo:"a6c2119701" the same row is displaying for 10 times

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show the 10 rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2009
    Posts
    10
    Hi ! r937...

    i dont know how to send the 10 rows ! so i send you one row and these values are keep on repeating for 10 times RegisterNo,ApplicationNo..... are column Name and the belowed things are values... is it in understandable manner...?

    RegisterNo ApplicationNo DOA AdmissionYear
    A6C2119701 1294 2006-04-17 00:00:00.000 2006

    YearType CentreCode SeminarCode CopurseGroup CopurseCode
    CALENDAR YEAR 970 Kuwait UGOUS 211

    Optional Medium ExamCentre PassportNo IssuedAt Issuedate ExpiryDate
    English 970 NULL NULL NULL NULL

    ApplicantName DOB ParentName Sex
    Hana Anwar Janoo 1986-11-16 00:00:00.000 Female

    Address Occupation PhoneNo EmailID QualifyDegree
    House No-7, Street No-7 Khaitan,

    University Enclosures ConditionEnclousures remark IsDeleted Income
    NULL 0 1

    ImgType Imgdata OldRegisterNo ReAdmitted UserName CourseGroupName
    NULL NULL NULL NULL kannan UG - OUS

    ProgramName CourseName
    Open University System B.B.A.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •