Create table field in oracle with default subquery value
1
SQL> select * from table1;
PRICES
----------
300
520
220
820
350
790
247
CREATE TABLE2 (DESCRIPT VARCHAR2(10), TOTALPRICES NUMBER(10) DEFAULT
(SELECT SUM(PRICES) FROM TABLE1);
I want something like that, when I
INSERT INTO TABLE2 VALUES ('TOTAL OF DAY');
SELECT * FROM TABLE2;
I want that to display :
DESCRIPT TOTALPRICES
-------------- -----------
TOTAL OF DAY 3247
oracle oracle11g create-table
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
|
show 1 more comment
1
SQL> select * from table1;
PRICES
----------
300
520
220
820
350
790
247
CREATE TABLE2 (DESCRIPT VARCHAR2(10), TOTALPRICES NUMBER(10) DEFAULT
(SELECT SUM(PRICES) FROM TABLE1);
I want something like that, when I
INSERT INTO TABLE2 VALUES ('TOTAL OF DAY');
SELECT * FROM TABLE2;
I want that to display :
DESCRIPT TOTALPRICES
-------------- -----------
TOTAL OF DAY 3247
oracle oracle11g create-table
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
Please don't shout, and make some more effort formatting your question. Be nice to the people you are asking for help!
– Laurenz Albe
Nov 19 '18 at 3:42
You cannot default a value that requires a calculation from some other table - it just isn't feasible. You can insert the calculated value - but when is this to be done? at midnight? at "close of business"? (what is that for you?)
– Used_By_Already
Nov 19 '18 at 4:07
I would recommend you to use Views for such cases instead of using another table.
– Kaushik Nayak
Nov 19 '18 at 4:18
I just want to create a table2 and inside that table2 create a field that receive or insert automatically the value that return this query "SELECT SUM(PRICES) FROM TABLE1" when i insert something in the other field of the same table2, i dont want to do something like that; INSERT INTO table2 VALUES ('some string here', SELECT SUM(prices) FROM table1);
– Andy Rodriguez Jimenez
Nov 19 '18 at 4:24
@AndyRodriguezJimenez Why don't you want to have theselect sum...in the insert statement? That is exactly where it should go, IMHO. Better yet, create a stored procedure that accepts parameters for the other columns, and use that to do the insert.
– Boneist
Nov 19 '18 at 10:44
|
show 1 more comment
1
1
1
SQL> select * from table1;
PRICES
----------
300
520
220
820
350
790
247
CREATE TABLE2 (DESCRIPT VARCHAR2(10), TOTALPRICES NUMBER(10) DEFAULT
(SELECT SUM(PRICES) FROM TABLE1);
I want something like that, when I
INSERT INTO TABLE2 VALUES ('TOTAL OF DAY');
SELECT * FROM TABLE2;
I want that to display :
DESCRIPT TOTALPRICES
-------------- -----------
TOTAL OF DAY 3247
oracle oracle11g create-table
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
SQL> select * from table1;
PRICES
----------
300
520
220
820
350
790
247
CREATE TABLE2 (DESCRIPT VARCHAR2(10), TOTALPRICES NUMBER(10) DEFAULT
(SELECT SUM(PRICES) FROM TABLE1);
I want something like that, when I
INSERT INTO TABLE2 VALUES ('TOTAL OF DAY');
SELECT * FROM TABLE2;
I want that to display :
DESCRIPT TOTALPRICES
-------------- -----------
TOTAL OF DAY 3247
oracle oracle11g create-table
oracle oracle11g create-table
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
edited Nov 19 '18 at 12:32
Brian Tompsett - 汤莱恩
4,2031338101
4,2031338101
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
asked Nov 19 '18 at 3:22
Andy Rodriguez JimenezAndy Rodriguez Jimenez
83
83
Please don't shout, and make some more effort formatting your question. Be nice to the people you are asking for help!
– Laurenz Albe
Nov 19 '18 at 3:42
You cannot default a value that requires a calculation from some other table - it just isn't feasible. You can insert the calculated value - but when is this to be done? at midnight? at "close of business"? (what is that for you?)
– Used_By_Already
Nov 19 '18 at 4:07
I would recommend you to use Views for such cases instead of using another table.
– Kaushik Nayak
Nov 19 '18 at 4:18
I just want to create a table2 and inside that table2 create a field that receive or insert automatically the value that return this query "SELECT SUM(PRICES) FROM TABLE1" when i insert something in the other field of the same table2, i dont want to do something like that; INSERT INTO table2 VALUES ('some string here', SELECT SUM(prices) FROM table1);
– Andy Rodriguez Jimenez
Nov 19 '18 at 4:24
@AndyRodriguezJimenez Why don't you want to have theselect sum...in the insert statement? That is exactly where it should go, IMHO. Better yet, create a stored procedure that accepts parameters for the other columns, and use that to do the insert.
– Boneist
Nov 19 '18 at 10:44
|
show 1 more comment
Please don't shout, and make some more effort formatting your question. Be nice to the people you are asking for help!
– Laurenz Albe
Nov 19 '18 at 3:42
You cannot default a value that requires a calculation from some other table - it just isn't feasible. You can insert the calculated value - but when is this to be done? at midnight? at "close of business"? (what is that for you?)
– Used_By_Already
Nov 19 '18 at 4:07
I would recommend you to use Views for such cases instead of using another table.
– Kaushik Nayak
Nov 19 '18 at 4:18
I just want to create a table2 and inside that table2 create a field that receive or insert automatically the value that return this query "SELECT SUM(PRICES) FROM TABLE1" when i insert something in the other field of the same table2, i dont want to do something like that; INSERT INTO table2 VALUES ('some string here', SELECT SUM(prices) FROM table1);
– Andy Rodriguez Jimenez
Nov 19 '18 at 4:24
@AndyRodriguezJimenez Why don't you want to have theselect sum...in the insert statement? That is exactly where it should go, IMHO. Better yet, create a stored procedure that accepts parameters for the other columns, and use that to do the insert.
– Boneist
Nov 19 '18 at 10:44
Please don't shout, and make some more effort formatting your question. Be nice to the people you are asking for help!
– Laurenz Albe
Nov 19 '18 at 3:42
Please don't shout, and make some more effort formatting your question. Be nice to the people you are asking for help!
– Laurenz Albe
Nov 19 '18 at 3:42
You cannot default a value that requires a calculation from some other table - it just isn't feasible. You can insert the calculated value - but when is this to be done? at midnight? at "close of business"? (what is that for you?)
– Used_By_Already
Nov 19 '18 at 4:07
You cannot default a value that requires a calculation from some other table - it just isn't feasible. You can insert the calculated value - but when is this to be done? at midnight? at "close of business"? (what is that for you?)
– Used_By_Already
Nov 19 '18 at 4:07
I would recommend you to use Views for such cases instead of using another table.
– Kaushik Nayak
Nov 19 '18 at 4:18
I would recommend you to use Views for such cases instead of using another table.
– Kaushik Nayak
Nov 19 '18 at 4:18
I just want to create a table2 and inside that table2 create a field that receive or insert automatically the value that return this query "SELECT SUM(PRICES) FROM TABLE1" when i insert something in the other field of the same table2, i dont want to do something like that; INSERT INTO table2 VALUES ('some string here', SELECT SUM(prices) FROM table1);
– Andy Rodriguez Jimenez
Nov 19 '18 at 4:24
I just want to create a table2 and inside that table2 create a field that receive or insert automatically the value that return this query "SELECT SUM(PRICES) FROM TABLE1" when i insert something in the other field of the same table2, i dont want to do something like that; INSERT INTO table2 VALUES ('some string here', SELECT SUM(prices) FROM table1);
– Andy Rodriguez Jimenez
Nov 19 '18 at 4:24
@AndyRodriguezJimenez Why don't you want to have the
select sum... in the insert statement? That is exactly where it should go, IMHO. Better yet, create a stored procedure that accepts parameters for the other columns, and use that to do the insert.– Boneist
Nov 19 '18 at 10:44
@AndyRodriguezJimenez Why don't you want to have the
select sum... in the insert statement? That is exactly where it should go, IMHO. Better yet, create a stored procedure that accepts parameters for the other columns, and use that to do the insert.– Boneist
Nov 19 '18 at 10:44
|
show 1 more comment
1 Answer
1
active
oldest
votes
1
There are few ways to implement this and one among them is to use a row level Trigger.
But, I would recommend you to use a View because it's not always worth having a table just to store values that can be generated from another table.
Creating a View
CREATE OR REPLACE VIEW v_table1 as select 'TOTAL OF DAY' as DESCRIPT
, SUM(prices) as TOTALPRICES
FROM Table1;
This allows you to simply query the view and the values will be generated from the underlying tables.
select * from v_table1;
If you feel that the underlying query of the view may be slow, you may convert it into a Materialised View.
Trigger should be your last option.
CREATE OR REPLACE TRIGGER tr_table2 BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
SELECT SUM(PRICES) INTO :NEW.TOTALPRICES FROM Table1;
END;
/
Demo
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53367886%2fcreate-table-field-in-oracle-with-default-subquery-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
There are few ways to implement this and one among them is to use a row level Trigger.
But, I would recommend you to use a View because it's not always worth having a table just to store values that can be generated from another table.
Creating a View
CREATE OR REPLACE VIEW v_table1 as select 'TOTAL OF DAY' as DESCRIPT
, SUM(prices) as TOTALPRICES
FROM Table1;
This allows you to simply query the view and the values will be generated from the underlying tables.
select * from v_table1;
If you feel that the underlying query of the view may be slow, you may convert it into a Materialised View.
Trigger should be your last option.
CREATE OR REPLACE TRIGGER tr_table2 BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
SELECT SUM(PRICES) INTO :NEW.TOTALPRICES FROM Table1;
END;
/
Demo
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
add a comment |
1
There are few ways to implement this and one among them is to use a row level Trigger.
But, I would recommend you to use a View because it's not always worth having a table just to store values that can be generated from another table.
Creating a View
CREATE OR REPLACE VIEW v_table1 as select 'TOTAL OF DAY' as DESCRIPT
, SUM(prices) as TOTALPRICES
FROM Table1;
This allows you to simply query the view and the values will be generated from the underlying tables.
select * from v_table1;
If you feel that the underlying query of the view may be slow, you may convert it into a Materialised View.
Trigger should be your last option.
CREATE OR REPLACE TRIGGER tr_table2 BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
SELECT SUM(PRICES) INTO :NEW.TOTALPRICES FROM Table1;
END;
/
Demo
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
add a comment |
1
1
1
There are few ways to implement this and one among them is to use a row level Trigger.
But, I would recommend you to use a View because it's not always worth having a table just to store values that can be generated from another table.
Creating a View
CREATE OR REPLACE VIEW v_table1 as select 'TOTAL OF DAY' as DESCRIPT
, SUM(prices) as TOTALPRICES
FROM Table1;
This allows you to simply query the view and the values will be generated from the underlying tables.
select * from v_table1;
If you feel that the underlying query of the view may be slow, you may convert it into a Materialised View.
Trigger should be your last option.
CREATE OR REPLACE TRIGGER tr_table2 BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
SELECT SUM(PRICES) INTO :NEW.TOTALPRICES FROM Table1;
END;
/
Demo
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
There are few ways to implement this and one among them is to use a row level Trigger.
But, I would recommend you to use a View because it's not always worth having a table just to store values that can be generated from another table.
Creating a View
CREATE OR REPLACE VIEW v_table1 as select 'TOTAL OF DAY' as DESCRIPT
, SUM(prices) as TOTALPRICES
FROM Table1;
This allows you to simply query the view and the values will be generated from the underlying tables.
select * from v_table1;
If you feel that the underlying query of the view may be slow, you may convert it into a Materialised View.
Trigger should be your last option.
CREATE OR REPLACE TRIGGER tr_table2 BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
SELECT SUM(PRICES) INTO :NEW.TOTALPRICES FROM Table1;
END;
/
Demo
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
answered Nov 19 '18 at 5:30
Kaushik NayakKaushik Nayak
18.6k41230
18.6k41230
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53367886%2fcreate-table-field-in-oracle-with-default-subquery-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please don't shout, and make some more effort formatting your question. Be nice to the people you are asking for help!
– Laurenz Albe
Nov 19 '18 at 3:42
You cannot default a value that requires a calculation from some other table - it just isn't feasible. You can insert the calculated value - but when is this to be done? at midnight? at "close of business"? (what is that for you?)
– Used_By_Already
Nov 19 '18 at 4:07
I would recommend you to use Views for such cases instead of using another table.
– Kaushik Nayak
Nov 19 '18 at 4:18
I just want to create a table2 and inside that table2 create a field that receive or insert automatically the value that return this query "SELECT SUM(PRICES) FROM TABLE1" when i insert something in the other field of the same table2, i dont want to do something like that; INSERT INTO table2 VALUES ('some string here', SELECT SUM(prices) FROM table1);
– Andy Rodriguez Jimenez
Nov 19 '18 at 4:24
@AndyRodriguezJimenez Why don't you want to have the
select sum...in the insert statement? That is exactly where it should go, IMHO. Better yet, create a stored procedure that accepts parameters for the other columns, and use that to do the insert.– Boneist
Nov 19 '18 at 10:44