Re: how do I rewrite this query optimized

Thanks LKBrwn for the update.

Thats what I have been trying to do. But nothing is clicking!!

Updatable view has to be key preserved. In the following case, the tables which don't get updated are not joining by their PK/UK. I probably have to summarize the result and put it in a temp table with Unique/primary key and use that in the updatable view.

Also, note that I have to get all the values from the third table pt. But the sql you have provided does not have it.

Thanks,

LKBrwn_DBA <member30625_at_dbforums.com> wrote in message news:<3135757.1058822561_at_dbforums.com>...
> You could try using a view:
>
> update (Select re.pk1col1
> , re.pk1col2
> , re.paid re_paid
> , p.pt_fk1col1
> , p.pt_fk1col2
> , p.rtype p_rtype
> From re, p
> where re.pk1col1 = p.pt_fk1col1
> and re.pk1col2 = p.pt_fk1col2)
> set re_paid = (re_paid
> + decode(P_rtype
> ,'xx', (-1 * P_pt_amt)
> ,'yy', (-1 * P_pt_amt)));
Received on Mon Jul 21 2003 - 21:47:21 CDT