I can get this far and generate the balances

select _fkAccounts , sum(_income) - sum(_expenditure) AS BAL
from transactions
join accounts on accounts._ID = transactions._fkaccounts
where accounts._adjustor = 1
group by _fkaccounts

but how do I put the result into the accounts table?

In DBISAM I'd have probably used a script with a temporary memory table and then joined that to do the update. In ElevateDB I'm lost.

Roy Lambert

I haven't done this in EDB but, wouldn't you do CREATE TABLE AS <queryexpression>, page 62
EDB sql manual?

dave m


>Roy Lambert wrote:

>but how do I put the result into the accounts table?
>In DBISAM I'd have probably used a script with a temporary memory table and then joined
that to do the update. In ElevateDB I'm lost.

Or INSERT INTO <TableName> VALUES <QueryExpression>, page 91 EDB sql manual.

dave m

Oh, you are trying to update.
Maybe I should stop posting while I am behind.
Looks like a CREATE PROCEDURE some variables may be needed.

Dave M

>Dave M wrote

>Or INSERT INTO <TableName> VALUES <QueryExpression>, page 91 EDB sql manual.

Dave


That was interesting

Your final guess is right - I should have said I'm trying to UPDATE.

Roy Lambert

>Roy Lambert wrote:

>That was interesting

That was written in a hurry before going out.
Basically, I hate reading other people's code, so mostly I ignore it, if possible.

I only see an UPDATE using values in the manual so, it looks like an update with SELECT
won't work.

Besides a procedure with variables, why can't you do a "script" by reloading a query with
each statement individually?
That's why I suggested CREATE AS, for making a temp table.

dave m

Roy,

<< In DBISAM I'd have probably used a script with a temporary memory table
and then joined that to do the update. In ElevateDB I'm lost. >>

Something like this should work:

UPDATE accounts SET Balance=
  (select  sum(_income) - sum(_expenditure) AS BAL
   from transactions where transactions._fkaccounts=accounts._ID)
WHERE accounts._adjustor = 1

--
Tim Young
Elevate Software
www.elevatesoft.com

Dave


At this point ElevateDB doesn't support scripts

I could do it with several lines of code but then I might as well do it mix'n'match with a combo of sql and navigational code.

Roy Lambert

Tim


Interesting. I don't think it works because it nulls the _Balance column, and I don't think that's right


Roy Lambert

Roy,

<< Interesting. I don't think it works because it nulls the _Balance column,
and I don't think that's right >>

It's a bug.  Essentially EDB is not setting up the linked columns properly
when the sub-select isn't live.

--
Tim Young
Elevate Software
www.elevatesoft.com