Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how do I rewrite this query optimized

Re: how do I rewrite this query optimized

From: Prince Kumar <gspk_at_yahoo.com>
Date: 22 Jul 2003 17:36:49 -0700
Message-ID: <629275ba.0307221636.6cd84b31@posting.google.com>


This is exactly what I tried. But, was getting "ORA-01427: single-row subquery returns more than one row". So I sum the amount and then update the col.

My sql is eaxtly like the following one, except I had select re.paid + sum(case when p.rtype in ('xx','yy') then -1 * pt.amt

                           else pt.amt end ) as amount

I put a sum around the decode/case.

Thanks for your follow-ups.

LKBrwn_DBA <member30625_at_dbforums.com> wrote in message news:<3138509.1058885452_at_dbforums.com>...
> OK, try this:
>
> update re
> set paid =
> (select re.paid
> + decode(p.rtype,'xx', (-1 * pt.amt)
> ,'yy', (-1 * pt.amt),pt.amt)
> from p, pt
> where p.pk1col1 = re.fk1col1
> and p.pk1col2 = re.fk1col2
> and pt.fk1col1 = re.pk1col1
> and pt.fk1col2 = re.pk1col2)
> where exists (
> select 1
> from p, pt
> where p.pk1col1 = re.fk1col1
> and p.pk1col2 = re.fk1col2
> and pt.fk1col1 = re.pk1col1
> and pt.fk1col2 = re.pk1col2);
Received on Tue Jul 22 2003 - 19:36:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US