Subquery returned more than 1 value error

Posted on 2011-04-25
Last Modified: 2012-05-11
I am trying to add a column to my results that shows a conditional total but I keep getting 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.

My query is below:

--Outstanding pledge balances

select g.giftid
            ,(select g.giftamount as BalanceDue from gifts_full where gifttype='p')
from gifts_full g left outer join payments_full p on g.giftkey=p.paylinkkey
          left outer join payments_VIEW_schedule_total_by_fy s on g.giftkey=s.paylinkkey
            left outer join names_VIEW_preferred n on g.giftid=n.nameid
            join chart_of_accounts c on g.giftrest=c.chart_code
            join division d on c.chart_div=d.table_code
            --left outer join (select g1.giftamount as g1.BalanceDue from gifts_full) g1 on g1.giftkey=p.paylinkkey
where g.gifttype in ('p', 'y', 'w') -- only include pledges and payments
            and g.giftjntkey<2 -- exclude joint recipients
            and g.gifttpledg>0

I am trying to show a balance due for any outstanding pledges - if the gift type is 'p' then giftamount as BalanceDue.

As always, any help is GREATLY appreciated!!

Question by:lcallah93
    LVL 69

    Accepted Solution

               ,(select g.giftamount as BalanceDue from gifts_full where gifttype='p')

               , case g.gifttype when 'p'  then g.giftamount else null end

    Author Closing Comment

    Ahh..the CASE statement.  I am a newbie to SQL but have heard many good things about the CASE statement.  It gave me exactly what I needed - thank you!!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now