[SQL-Ledger-users] Re: SQL update statement

[John O'G. <ogo...@ho...>]

To selectively update rows: (I have made up column and table names)

UPDATE tx
	SET (balance )= (SELECT expr from .... WHERE )
WHERE txdate between today - 7 and today

Note the 'today' is appropriate for Informix. There will be an
equivalent in PostgreSQL.

To iterate through rows keeping a running balance, you will need
to open a cursor and use a FOREACH statement.

John O'Gorman

>From: li...@li... (Linas Vepstas)
>Reply-To: sql...@li...
>To: "Donald L Greer Jr." <dg...@au...>
>CC: sql...@li..., li...@li..., 
>gnu...@gn...
>Subject: Re: [SQL-Ledger-users] OT: Web-based Checking Software
>Date: Sat, 5 May 2001 23:17:14 -0500
>
>
>Hi Don,
>Good to hear from you, been a long time ...
>
>On Sat, May 05, 2001 at 04:17:40PM -0500, Donald L Greer Jr. was heard to 
>remark:
> >   Linas,
> >   Rather than keep a running total on a per-transaction basis, why not
> > keep a daily balance.  Then if you change a transaction 2 months ago,
> > you've got to apply one change to 60 entries and the change is always
> > the same (+/- X).
>
>right.  This is what I called a 'balance checkpoint' in the original
>note. (could be daily, could be every N transactions, whatever).
>
> > A single query can do that lickity-split.
>
>Umm. How good are you at sql?  I can't say that I know how to
>select 60 rows, add a value to a field, and update those rows,
>in a single sql statement.   Have I just not mastered the finer points
>of sql?  Can you sketch out that statement?
>
> >   Then when you display, you calculate the running total from the
> > starting balance of the first day displayed, allowing you to process
> > (hopefully) very few transactions.  Alternately the running total could
> > be based on some arbitrary number of transactions (say the balance every
> > hundredth transaction to avoid problems with high-volumes of
> > transactions (e.g. a video store or a fast-food resteraunt that does
> > 1000+ transactions/day).
> >   Just a thought
>
>The problem with what you describe is this:
>Say I want to display all transactions since noon, but the last
>balance checkpoint was midnight.  Thus, I have to query all transactions
>since midnight, compute the running blance, and display only those
>since noon.   It requires a little extra work, but its doable.
>
>Now imagine I want to show all transactions whose memo field starts
>with the letter A...
>
>see what the problem is? first I get all with letter A.  Then I need to
>figure out what checkpoint lies before the earliest transaction, get
>that, and then get all transactions since then, then compute the running
>balance ...  Ugh.
>
>Not too hard to explain, but rather tedious to code up.    Just tedious
>enough that I haven't done it ...
>
>--linas
>
>
> >
> > Linas Vepstas wrote:
> > >
> > [...]
> > > Well, its 'almost' usable, except for one interesting technical 
>problem
> > > I'd like to tell you about.  GnuCash likes to display accounts in a
> > > 'checkbook register style': that means that there is a running balance
> > > displayed in one of the columns (far right column).
> > >
> > > Say one has a millions transactions in the database. Clearly its 
>insane
> > > to fetch all of them to compute a running balance.  We could store a
> > > running balance with each transaction, but this has a nasty update
> > > semantic: changing one transaction would require updates to hundreds
> > > or thousands of others that occur at a later date.  We talked about
> > > storing 'balance checkpoints' to solve that problem.  But this
> > > introduces another problem: I would need to query a contiguous set of
> > > transactions between checkpoints.  At this point, the sql queries 
>start
> > > getting complex, there's a lot of traffic to the database, and yuck,
> > > so we left it to ferment a bit more.    If you are willing to ignore
> > > the running balance column, then I think the multi-user mode is not 
>far
> > > off.  (there's some minor misc stuff that needs to be brushed up, I
> > > forget what).
> > >
> > [...]
> >
> > --
> > --------------------------------------------------------
> > Donald L. Greer, Jr                  dg...@Au...
> > System Administrator                 Voice: 512-835-8005
> > AustinTX.COM                    http://www.AustinTX.COM/
> >   All opinions are my own.  Flame me directly.
> >
> > "I don't necessarily believe software should be free...
> > but if you pay for it, it should work!" -- Me
>
>--
>Linas Vepstas -- li...@gn... -- http://www.gnumatic.com/
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com