CODEWITHSUNDEEP
mysql
glarkou
glarkou

Reputation: 7101

Field as view result

Is it possible to "automatically" set the value of a column to the result of a query?

For example I would like to have a column e.g. balance which when I request it, it should return the running total of some transactions but I prefer if that would have been pre-calculated.

Can I somehow "link" a column to the result of a View or a Function?

PS. We use MySQL Cluster

The query looks like the following:

SET @id = 'customerid'; 
SET @accId = 'accid';   

Select  sum(trans.t) - sum(trans.c) as Balance  
from (  select          *, 
        case when ACNO2=@accId then a1 * 1 else a1 * -1 end as t, 
        case when (TrCode= '40' and ACNO2=@accId) then 0 else ChargeAmount1 end as c    
        from OldTransactions    
        where (((CLNO1=@id and CLNO2=@id) or (CLNO1=@id or CLNO2=@id)) and TStatus='OK')) as trans 
where (ACNO1=@accId or ACNO2=@accId)

Upvotes: 2

Views: 37

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

http://dev.mysql.com/doc/refman/5.7/en/create-table.html says in part:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column.

(emphasis mine)

So you can't set use the DEFAULT syntax to define a column as the result of a subquery.

As an alternative, you could create triggers on the transactions table you want to monitor, so that any change to the transactions updates your balance column.

You should create a trigger for each action: insert, delete, and update.

CREATE TRIGGER trx_ins_balance ON transactions AFTER INSERT
FOR EACH ROW
  UPDATE accounts SET accounts.balance = accounts.balance + NEW.amount
  WHERE accounts.accountid = NEW.accountid;

CREATE TRIGGER trx_del_balance ON transactions AFTER DELETE
FOR EACH ROW
  UPDATE accounts SET accounts.balance = accounts.balance - OLD.amount
  WHERE accounts.accountid = NEW.accountid;

CREATE TRIGGER trx_upd_balance ON transactions AFTER UPDATE
FOR EACH ROW
  UPDATE accounts SET accounts.balance = accounts.balance - OLD.amount + NEW.amount
  WHERE accounts.accountid = NEW.accountid;

The above is just an example. Don't ask me for a more tailored example, because you shouldn't be using code verbatim from Stack Overflow anyway. You should understand how to write your own triggers if you're going to use them.

Another solution is to create a VIEW so that your balance column is actually an expression to read the transactions table. But this won't be pre-calculated. It will re-query the transactions every time you query the view, which might be an expensive solution.

Upvotes: 1

Related Questions