Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Subquery returned more than 1 value error

Posted on 2011-04-25
2
Medium Priority
?
275 Views
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
            ,g.gifteffdat
            ,g.gifttype
            ,g.giftjntamt
            ,(select g.giftamount as BalanceDue from gifts_full where gifttype='p')
            ,g.giftkey
            ,g.giftplgkey
            ,g.giftjntrat
            ,g.giftorigpl
            ,g.gifttpledg
            ,g.giftsolnam
            ,g.giftclass2
            ,g.gifttender
            ,p.paylinkkey
            ,p.payid
            ,p.payduedate
            ,p.paydueamt
            ,p.paybalamt
            ,s.sumJOINTctr
            ,s.sumNOBILLctr
            ,n.namesmashd
            ,n.nameformn
            ,n.nameplural
            ,c.chart_val
            ,d.table_val
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!!

Lisa
0
Comment
Question by:lcallah93
2 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 35461421
replace:
           ,(select g.giftamount as BalanceDue from gifts_full where gifttype='p')

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

Author Closing Comment

by:lcallah93
ID: 35461585
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!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

581 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