Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how do I rewrite this query optimized
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