Is it possible to create a table with a column that combines two column values? something like this:
create table test1 ( number1 decimal(6,2), number2 decimal(6,2), total decimal(6,2) DEFAULT (number1+number2) );
1
-
do you need the possibility to assign a different value to that column as well? or is it enough to have it always computed that way?Markus Winand– Markus Winand2010-09-24 13:05:04 +00:00Commented Sep 24, 2010 at 13:05
Add a comment
|
3 Answers
Yes, in 11G. It is called a "virtual" column. The syntax is:
create table test1
( number1 number(6,2),
number2 number(6,2),
total number(6,2) generated always as (number1+number2) );
(There isn't a DECIMAL datatype in Oracle AFAIK, so I used NUMBER instead.)
See also: documentation
NB The "generated always" keywords are optional.
The types of expression allowed after "as" are described here.
4 Comments
Bob Jarvis - Слава Україні
Just an aside - DECIMAL works fine in Oracle, but I believe it's translated to NUMBER internally.
Jay
Is it also possible to use the "generated always as" to use an inner select?
Jay
like generat always as (select sum(*) from table) or something like that?
Tony Andrews
No, you would need to calculate via a trigger in that scenario - see Bob Jarvis's answer.
You can do this using a trigger:
create table test1
(number1 decimal(6,2),
number2 decimal(6,2),
total decimal(6,2));
CREATE TRIGGER test1_bi
BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
IF :new.total is NULL THEN
:NEW.TOTAL := :new.number1 + :new.number2;
END IF;
END test1_bi;
INSERT INTO test1(number1, number2) VALUES(1, 2);
SELECT * FROM test1;
After the SELECT you'll find that TOTAL has a value of 3, as expected.
Share and enjoy.
answered Sep 24, 2010 at 12:54
Bob Jarvis - Слава Україні
50.2k10 gold badges81 silver badges119 bronze badges
Comments
You need to use computed columns.
