Re: Avoiding cursors
james wrote:
> It turns out in order to get it right I have to
> sum(a2.balancewsubs) where a2.id = p.account in a
> subquery because
> "a2.balancewsubs = if(a1.id=a2.id,a2.balancewsubs,
> a2.balancewsubs+ p.amount)"
> adds p.amount onto the original a2.balancewsubs
> figure (which is always 0 because it is reset at
> the beginning of the sub) rather than a
> culmulative total as it goes through the table.
Ok i think I understand - it's because several leaf's of the tree (the accounts that correspond directly to a posting) end up in the same branch, but the amount is not updated cumulatively?
Anyway, you could retry you original approach - that is, us a cursor (LOL, rembering your subject title). What I mean is this: In the original post the problem was with the recursion. You managed to get rid of that so now you can properly define the set you need to update in a single query. Ok, now the update itself is giving you a problem, because the amounts are not accumulated (never thought of that btw). Now, what I want to propose is something like this:
1) set up a cusor using this new set, eg:
declare csr_account cursor for
select a2.id as account_to_update
, p.amount
, if(a1.id = a2.id, false,true) as balancewsubs -- note: you could write this as (a1.id!=a2.id), but i like it better explicit
from postings p
inner join acclist a1
on p.account = a1.id
inner join acclist a2
on a1.lft >= a2.lft
and a1.rgt <= a2.rgt
;
2) loop through this cursor, record by record and do a
fetch csr_account into v_id, v_amount, v_wsubs
3) suppose you inside the loop, perform an update like this:
update acclist
set balance = if (v_wsubs, balance,balance + v_amount)
, balancewsubs = if (v_wsubs, balancewsubs + v_amount, balancewsubs)
where id = v_id
;
and see how that performs.
>
> Problem being that I cant use the same table in a
> sub query. So eventually I have a statement after
> it to calculate the balancewsubs and insert it
> into another table. Then another statement that
> takes the balancewsubs out of the temporary table
> and puts it back in the correct place.
>
> It works fast (about 3 seconds instead of the
> original 25) but if anyone knows of a way to get
> round this "same table sub query limit" please let
> me know.
I think your handling (temp table ) is appropriate for this situation. I find this subquery restriction a pain too.
Edited 1 time(s). Last edit at 02/01/2006 04:03PM by Roland Bouman.