Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Problem
Oracle Problem [message #303428] Fri, 29 February 2008 06:08 Go to next message
psnow1985
Messages: 6
Registered: February 2008
Junior Member
Hi,

Just a quick question. I have a database with a number of tables. This database is basically a booking system for a holiday company. I have a table called "SiteTents" which holds the tents for each site. In this table there is a field called "TentCost(NUMBER(20)".

I have another table called billing. In this table I have a field called "totaltentcost(Number(10)". I have "Clients" that can book tents. E.g there is a client which has booked 6 tents (in a table called "TentBooking") At the moment I have to work out the told tent cost by hand. My question is; is there any way to work out the total cost when inserting the data from multiple tables (All the tables that are needed to do the calculations are linked)?

Many thanks in advance.

[Updated on: Fri, 29 February 2008 06:09]

Report message to a moderator

Re: Oracle Problem [message #303435 is a reply to message #303428] Fri, 29 February 2008 06:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
At the moment I have to work out the told tent cost by hand

what do you mean by this?
Quote:
(All the tables that are needed to do the calculations are linked)?

if all the tables are linked then what is the problem that you are facing?


regards,
Re: Oracle Problem [message #303437 is a reply to message #303428] Fri, 29 February 2008 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Posting a test case: create table and insert statements as well as the result you want with these data should be great to understand your problem.
Also posting the your Oracle version (3 decimals) to know what part of SQL you can handle.

Regards
Michel
Re: Oracle Problem [message #303439 is a reply to message #303435] Fri, 29 February 2008 06:24 Go to previous messageGo to next message
psnow1985
Messages: 6
Registered: February 2008
Junior Member
At the moment I have to work out the calculation in my head and write it down when I insert the value.

E.G

INSERT
INTO Billing
VALUES(2,2,20,'Mid',6,26)

Where Billing is constructed as:

CREATE TABLE Billing
(BillingID INT,
ClientID INT,
TotalTentCost NUMBER(10),
Season VARCHAR2(10),
TotalFacCost NUMBER(10),
FinalCost NUMBER(20),
PRIMARY KEY (BillingID));

AlTER TABLE Billing
ADD CONSTRAINT fk_Billing
FOREIGN KEY(Season)
REFERENCES Season (Season)
ADD CONSTRAINT fk_Billing2
FOREIGN KEY(ClientID)
REFERENCES Client (ClientID);


So what I am trying to do for the database to work out the total cost for the tents. However the only way to work this out is by doing a SELECT statement. But I need to have the calculation calculated when the data is inserted.

Regards,

Peter
Re: Oracle Problem [message #303442 is a reply to message #303439] Fri, 29 February 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you do.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Once again what is your version?

Regards
Michel
Re: Oracle Problem [message #303445 is a reply to message #303442] Fri, 29 February 2008 06:36 Go to previous messageGo to next message
psnow1985
Messages: 6
Registered: February 2008
Junior Member
I am using Oracle 9i.

Thanks for the link to the post about the formating of posts.
Re: Oracle Problem [message #303446 is a reply to message #303442] Fri, 29 February 2008 06:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ehhhh.... Do you simply want to do ....

INSERT
INTO Billing
VALUES(2,2,20,'Mid',6, 20 + 6)


Instead of

INSERT
INTO Billing
VALUES(2,2,20,'Mid',6, 26)


Do you actually write the insert by hand??

Re: Oracle Problem [message #303447 is a reply to message #303446] Fri, 29 February 2008 06:42 Go to previous messageGo to next message
psnow1985
Messages: 6
Registered: February 2008
Junior Member
Yes I write the insert by hand. Although writing 20 + 6 solves my problem I was trying to find a way that the figures could be taken from fields from other tables.

Regards,
Re: Oracle Problem [message #303453 is a reply to message #303428] Fri, 29 February 2008 07:09 Go to previous messageGo to next message
psnow1985
Messages: 6
Registered: February 2008
Junior Member
So I guess doing the 20 + 6 way might be the only way of doing this.
Re: Oracle Problem [message #303456 is a reply to message #303453] Fri, 29 February 2008 07:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are other ways, but they are not really practical to do while still writing the insert by hand.

You could write a stored procedure with the needed parameters which does the calculations and then inserts the values.

You could calculate some values with triggers during the insert.

You could calculate some values in the front-end before the insert.

But those are all design decisions what are impossible to decide without the complete requirements for the project.
Re: Oracle Problem [message #303458 is a reply to message #303456] Fri, 29 February 2008 07:20 Go to previous message
psnow1985
Messages: 6
Registered: February 2008
Junior Member
Many thanks everyone. I shall follow your advice and simply add the numbers up by hand.

Many thanks.
Previous Topic: SEQUENCEs continuous
Next Topic: SQL
Goto Forum:
  


Current Time: Sun Dec 04 21:07:39 CST 2016

Total time taken to generate the page: 0.11595 seconds