Page 1 of 6 123 ... LastLast
Results 1 to 15 of 81
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: Subquery returned more than 1 value

    I appreciate anyone who can give me help with the following error:


    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:


    [SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.]
    System.Data.SqlClient.SqlDataReader.Read() +157
    DotNetNuke.PortalSettings.GetPortalSettings(Int32 tabId, String PortalAlias)
    DotNetNuke.PortalSettings..ctor(Int32 tabId, String PortalAlias, String ApplicationPath)
    DotNetNuke.Global.Application_BeginRequest(Object sender, EventArgs e)
    System.Web.SyncEventExecutionStep.System.Web.HttpA pplication+IExecutionStep.Execute() +60
    System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean& completedSynchronously) +87

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Really simple, your sql has a sub query, something like this

    Code:
    Select * from orders where customer_id = 
      (select customer_id from customer where name ='bob')
    Now the query above will only run providing the sub query only returns one record, eg. there is only one Bob.

    If there is more then one Bob there will be more then on customer_id return so the ='s operator won't work (you would have to use in or something).

    Does that make sense?

  3. #3
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: Subquery returned more than 1 value

    You have two options:
    1) modify your query so that a set of values is allowed. Example: Use IN instead of "=" (equal sign)
    2) modify your subquery so that it returns only one row. Example: Use MAX() function to reduce many rows to one.

    Regards, Bill (aka HurMaVi)

  4. #4
    Join Date
    Mar 2004
    Posts
    1

    Re: Subquery returned more than 1 value

    I read this as I had the same problem. The other replies gave me the clue I needed as my SQL was this simple...

    Delete from tblContact
    Where ClientCode = 'CG'

    So how could I have a sub-query...

    Well, I had a trigger that was causing the problem. Maybe that is your problem too.

    S.

  5. #5
    Join Date
    Feb 2004
    Posts
    6
    Hi,
    I have the same problem too.Please look at this query

    select (select count(diffdueout) from boultlog where diffdueout =0 group by floornum ),count(due),
    (select count (diffdueout) from boultlog where diffdueout = 0 group by floornum)*100/count(due)
    from boultlog group by floornum

    this query runs fine if i give ::...where diffdueout =0 and floornum = '5'.It gives me all the values right.here iam trying to calculate the percentage (select count (diffdueout) from boultlog where diffdueout = 0 and floornum = '5')*100/count(due)

    SO,my question now is how do i calculate the percentages for all the floors?If iam trying to put group by floornum it gives me the error.

    Thanks

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to add floornum to the select list
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    6

    Red face

    Thank you for your response.

    But,it doesnt work.Any other suggestion is appreciated.

    Thanks

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You know that

    count(due),


    is all the non null rows in the table

    Why does this make sense to this query?

    What platform are you working in?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Posts
    6
    yes ,iam sure that count(due) is not null and due column has all the valid values.Iam working with sqlserver.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not what I meant....

    Your comparing all non null rows in the entire table to a count based on 1 specific floor...does that make sense?

    What value does that have

    And why not post here...

    LOTS of people to help

    http://www.dbforums.com/forumdisplay.php?forumid=7
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Posts
    6
    I got your point now..

    Thanks for your help.

    i tried this and it worked ...


    SELECT FloorNum, COUNT(WorkOrderNum) AS Number_of_jobs, SUM(BlackAWhiteCopies) AS Number_of_copies, AVG(TurnAroundTime) AS TurnAroundTime,
    (SELECT COUNT(diffdueout)
    FROM boultlog
    WHERE floornum = blog.floornum AND (DiffDueOut = 0)) * 100 / COUNT(Due) AS percentage_delivered_on_time, SUM(OriginalsNum)
    AS Number_0f_Originals, SUM(CopiesNum) AS NumberOfCopiesRequested
    FROM BoultLog blog
    GROUP BY FloorNum

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    GREAT...and congrats...

    Come and have a margarita...

    http://www.dbforums.com/showthread.p...57#post3651357
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2009
    Posts
    1
    hi

    i'm a beginner...
    i have the same problem too.Please look at this query ...

    SELECT A.IDMHS,
    (SELECT DISTINCT NPM
    FROM MS_MAHASISWA
    WHERE (A.IDMHS = NO_USM) AND (STATUS_AKTIF = 'A')) AS NPM,
    (SELECT DISTINCT NM_MAHASISWA
    FROM MS_MAHASISWA AS MS_MAHASISWA_1
    WHERE (A.IDMHS = NO_USM)) AS MAHASISWA,
    (SELECT DISKON
    FROM PUPD_T_PYTDET
    WHERE (IDBIAYA = 5) AND (IDMHS = A.IDMHS)) AS SUMBANGAN, SUM(A.DISKON) AS KEWAJIBAN, SUM(B.BAYAR) +
    (SELECT BAYAR
    FROM PUPD_T_BAYDET
    WHERE (IDBIAYA = 5) AND (IDMHS = A.IDMHS)) AS BAYAR, SUM(B.BALANCE) * - 1 AS SISA, SUM(A.DISKON) - SUM(B.BAYAR) AS HITUT
    FROM PUPD_T_BAYDET AS B INNER JOIN
    PUPD_T_PYTDET AS A ON A.IDMHS = B.IDMHS AND A.IDBIAYA = B.IDBIAYA RIGHT OUTER JOIN
    PUPD_T_PYT AS F ON F.IDMHS = A.IDMHS RIGHT OUTER JOIN
    PUPD_T_BAYAR AS M ON M.IDMHS = A.IDMHS
    WHERE (F.NPM LIKE '__09&#37;') AND (A.IDBIAYA <> 5)
    GROUP BY A.IDMHS
    ORDER BY NPM

    please help me...thanks before

  14. #14
    Join Date
    Jul 2010
    Posts
    39
    I have the following query and it is giving the same error as subquery returned more than one value, can anybody help in which subquery the error might be?

    select
    bd.[unique id], BD.Billing, [service date 1], [CPT Code], BD.[Transaction Code], Units, Extended,

    (
    SELECT ( i.[detail note])
    FROM [billing detail] o
    left outer join [billing detail] i ON (i.billing = o.billing and o.[transaction code] = i.[cpt code] )
    where o.item = i.item and (i.[transaction Code] = 'PIP' or
    i.[transaction Code] = 'APPEA0001' OR
    i.[transaction Code] = 'ATTYPYMT' OR
    i.[transaction Code] = 'INPRO0000' OR
    i.[transaction Code] = 'INTPMTS' OR
    i.[transaction Code] = 'LPMT0000' OR
    i.[transaction Code] = 'MRPYMT' OR
    i.[transaction Code] = 'NONRESP' OR
    i.[transaction Code] = 'PARTI0000' OR
    i.[transaction Code] = 'PARTI0001' OR
    i.[transaction Code] = 'PPCA' OR
    i.[transaction Code] = 'PPCC' OR
    i.[transaction Code] = 'PPCH' OR
    i.[transaction Code] = 'PPIP' OR
    i.[transaction Code] = 'PSIO' OR
    i.[transaction Code] = 'PTIP' OR
    i.[transaction Code] = 'SETTL0000' OR
    i.[transaction Code] = 'SETTOPAT' OR
    i.[transaction Code] = 'SIP ' OR
    i.[transaction Code] = 'STMT' OR
    i.[transaction Code] = 'SUPCA' OR
    i.[transaction Code] = 'SUPCC' OR
    i.[transaction Code] = 'SUPCH' OR
    i.[transaction Code] = 'TIP' OR
    i.[transaction Code] = 'VOUCHER' OR
    i.[transaction Code] = 'VOUCHPHON' ) and bd.[unique id] = o.[unique id]
    ) as Detail_Note
    ,

    BH.Location, FlatFee,

    (
    SELECT ABS( i.extended)
    FROM [billing detail] o
    left outer join [billing detail] i ON (i.billing = o.billing and o.[transaction code] = i.[cpt code] )
    where o.item = i.item and (i.[transaction Code] = 'PIP' or
    i.[transaction Code] = 'APPEA0001' OR
    i.[transaction Code] = 'ATTYPYMT' OR
    i.[transaction Code] = 'INPRO0000' OR
    i.[transaction Code] = 'INTPMTS' OR
    i.[transaction Code] = 'LPMT0000' OR
    i.[transaction Code] = 'MRPYMT' OR
    i.[transaction Code] = 'NONRESP' OR
    i.[transaction Code] = 'PARTI0000' OR
    i.[transaction Code] = 'PARTI0001' OR
    i.[transaction Code] = 'PPCA' OR
    i.[transaction Code] = 'PPCC' OR
    i.[transaction Code] = 'PPCH' OR
    i.[transaction Code] = 'PPIP' OR
    i.[transaction Code] = 'PSIO' OR
    i.[transaction Code] = 'PTIP' OR
    i.[transaction Code] = 'SETTL0000' OR
    i.[transaction Code] = 'SETTOPAT' OR
    i.[transaction Code] = 'SIP ' OR
    i.[transaction Code] = 'STMT' OR
    i.[transaction Code] = 'SUPCA' OR
    i.[transaction Code] = 'SUPCC' OR
    i.[transaction Code] = 'SUPCH' OR
    i.[transaction Code] = 'TIP' OR
    i.[transaction Code] = 'VOUCHER' OR
    i.[transaction Code] = 'VOUCHPHON' ) and bd.[unique id] = o.[unique id]
    ) as Insurance

    from FlatFees as FF, [billing detail] as BD Left Join [Billing Header] as BH on BD.Billing = BH.Billing
    WHERE ([CPT Code] = CPTCode) and FFLocation = [Location] and extended > 0
    order by [Service Date 1] asc

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hackingarena View Post
    can anybody help in which subquery the error might be?
    well, since both of the subqueries are exactly the same other than the SELECT clause, the answer is...

    ... bofadem

    in other words, you have to rewrite the query

    i'd help you do it, but there are a lot of things wrong with it
    Code:
    SELECT BD.[unique id]
         , BD.Billing
         , [service date 1]
         , [CPT Code]
         , BD.[Transaction Code]
         , Units
         , Extended
         , ( SELECT i.[detail note]
               FROM [billing detail] o
             LEFT OUTER 
               JOIN [billing detail] i 
                 ON i.billing = o.billing 
                AND o.[transaction code] = i.[cpt code]
              WHERE o.item = i.item 
                AND i.[transaction Code] IN ( 'PIP','APPEA0001','ATTYPYMT','INPRO0000','INTPMTS','LPMT0000','MRPYMT','NONRESP','PARTI0000','PARTI0001','PPCA','PPCC','PPCH','PPIP','PSIO','PTIP','SETTL0000','SETTOPAT','SIP ','STMT','SUPCA','SUPCC','SUPCH','TIP','VOUCHER','VOUCHPHON' ) 
                AND BD.[unique id] = o.[unique id]
           ) AS Detail_Note
         , BH.Location
         , FlatFee
         , ( SELECT ABS(i.extended)
               FROM [billing detail] o
             LEFT OUTER 
               JOIN [billing detail] i 
                 ON i.billing = o.billing 
                AND o.[transaction code] = i.[cpt code] 
              WHERE o.item = i.item 
                AND i.[transaction Code] IN ( 'PIP','APPEA0001','ATTYPYMT','INPRO0000','INTPMTS','LPMT0000','MRPYMT','NONRESP','PARTI0000','PARTI0001','PPCA','PPCC','PPCH','PPIP','PSIO','PTIP','SETTL0000','SETTOPAT','SIP ','STMT','SUPCA','SUPCC','SUPCH','TIP','VOUCHER','VOUCHPHON' ) 
                AND BD.[unique id] = o.[unique id]
           ) as Insurance
      FROM FlatFees as FF
         , [billing detail] as BD 
    LEFT 
      JOIN [Billing Header] as BH 
        ON BD.Billing = BH.Billing
     WHERE [CPT Code] = CPTCode 
       AND FFLocation = [Location] 
       AND extended > 0 
    ORDER 
        BY [Service Date 1] asc
    for example, the subqueries use a LEFT OUTER JOIN, but then put a condition on the right table (i.[transaction Code] must be a specific value), the outer query FROM clause contains an implicit join, and multiple columns are not properly qualified with their table name/alias

    clean up your query and i might be able to help you rewrite the subqueries as a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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