Link to home
Start Free TrialLog in
Avatar of lcallah93
lcallah93Flag for United States of America

asked on

Subquery returned more than 1 value error

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
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lcallah93

ASKER

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!!