CODEWITHSUNDEEP
sqldb2ibm-cloud
Mike Pala
Mike Pala

Reputation: 806

Formula / Calculation columns in a database table

I dno't think this is possible or that it makes sense, but most people here are much smarter then me and it never hurts to ask. Is it possible to create a table in a database that is a formula. I don't mean a query. I mean let's say I have a table and one of the columns is "price" another one is "quantity" and I would like a column "cost" to calculate (price * quantity) .... as the data gets imported. So that later I can just:

select * from table;

If you tell me this is a stupid question I'll understand but I got it from an internal client and need to investigate it. The db I am running with is DB2 on BlueMix.

Upvotes: 0

Views: 457

Answers (4)

Tech Crazy Karthick
Tech Crazy Karthick

Reputation: 11

You can use computed columns to take an action. In computed columns you will give the default formula as 

ALTER TABLE <TABLENAME> ADD <COLUMN_NAME> AS (<COLUMN_NAME1> * <COLUMN_NAME2>);

Thus the above query is used for only same table u want add that column. If you want to add another table there are many ways.

Example

  1. Transaction (It cause may deadlocks)
  2. Triggers (it may cause server may slow)
  3. Stored Procedures (Normal insert statement with using select clause)
  4. Without Store procedure we can right query with normal insert statement.

Example:

CREATE TABLE Cost_Table
(
Cost int
);

INSERT INTO Cost_Table (Cost)<BR>
SELECT price * quantity FROM Table_name;

This is optimized way . we can use thus above query instead of trigger. Thus Above query use inside of transaction and Store procedure.

Upvotes: 1

WarrenT
WarrenT

Reputation: 4542

If you're not careful, it could lead to a major security breach. Look up "SQL code injection".

But to do it, another route might be to use EXECUTE IMMEDEATE inside a stored procedure.

Upvotes: 0

Jinesh Shah
Jinesh Shah

Reputation: 952

You can set default value for cost column as

ALTER TABLE table-name ALTER COLUMN cost set cost=price*quantity;

Or you can create trigger as follows,

db2 create trigger trigger_name after insert on table_name 
for each row mode db2sql begin atomic update tablle_name 
set cost=qty*price; end

Upvotes: 3

vladatr
vladatr

Reputation: 626

Try with triggers. You have a tutorial here: https://www.tutorialspoint.com/db2/db2_triggers.htm

A database trigger is procedural code that is automatically executed in response to certain events

Upvotes: 1

Related Questions