Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
whitebear
Starting Member
2 Posts |
 Posted - 2010-11-19 : 10:30:56
|
| I'm using Oracle and creating a basic database just to have a practice with.The issue is that i want a subtotal column in one of the tables which calculates the customers order from all the order details (for example the customer may buy two pairs of shoes and one pair of trainers). The syntex i've used so far is:CREATE TABLE CMORDER(ORDERNO NUMBER(8) NOT NULL PRIMARY KEY, CUSTID NUMBER(8) NOT NULL, SUBTOTAL NUMBER(7) NOT NULL, VAT NUMBER(7) NOT NULL, TOTAL NUMBER(7) NOT NULL, PAYMENTTYPE VARCHAR2(15) NOT NULL);But as you can see there is no query there for the "subtotal" column. Do you do the query once you've created all the tables and foreign key constraints or should I add the query in the syntex now? It will be a similar scenario for "VAT" and "Total", but hopefully your answer will solve all three issues.Thanks for your help!Tom |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-19 : 10:39:37
|
| Try an Oracle forum?I think you want a running total - i.e. subtotal = sum of all preceding orders (including this one). For this you need to define what preceding means (ordered by orderno?).This would normally be calcaulted by whatever inserts the row into this table.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-19 : 10:39:58
|
| You may be better off posting this in a Oracle forum like www.orafaq.comThis is a forum dedicated for MS SQL server and solutions given here will be mostly SQL server specific------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
whitebear
Starting Member
2 Posts |
Posted - 2010-11-19 : 10:42:22
|
| Ooops! Thanks anyway!Tom |
|
|
|
|
|
|
Trying oracle for the 1st time