sum() with values from another table

[#1]

Hi guys,

how can I use a columm, which i created new from another table like this

Table1: Amount,

     Date,

     If(Amount, Amount*(-1)) as NEW.Amount

Table2:

Load Amount,

     Date,

     sum(NEW.Amount) as SUM.SELL where (.X..) group by ..

SQL Select Date, Amount ... FROm ...

Table3:

Load Amount,

     Date,

     sum(NEW.Amount) as SUM.BUYwhere (.Y..) group by ..

SQL Select Date, Amount ... FROm ...

__

the NEW.Amount is now in the database, so how can I use it across the tables?

[#2]

I get an error if I use that ..

"New.Amount" not found SQL SELECT...FROM..

[alexandros17]

In the script you may use a field only if it is contained in the table so if you need this you habìve to join somehow the tables.

[Anonymous · Accepted solution]

You can use NEW.Amount by doing resident load of table 1. See below,

Table1: Amount,

     Date,

     If(Amount, Amount*(-1)) as NEW.Amount

Table2:

Load Amount,

     Date,

     sum(NEW.Amount) as SUM.SELL

Resident Table1

where (.X..) group by ..;

Table3:

Load Amount,

     Date,

     sum(NEW.Amount) as SUM.BUY

Resident Table1

where (.Y..) group by ..;

[#5]

But it wont work if I use this:

CONNECT TO DB;

Load Date,

     Amount,

     If(Amount, Amount*(-1)) as NEW.Amount,

     sum(New.Amount) as SUM01 WHERE (..X) Group By Date, Amount, NEW.Amount

SQL SELECT Date, Amount

FROM DB.DB1

I would also get en error with: "not found the field New.Amount" in sql..