Home » SQL & PL/SQL » SQL & PL/SQL » Calculating from multiple tables
Calculating from multiple tables [message #286990] Mon, 10 December 2007 16:30 Go to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
Hi All.

I was hoping somebody might help with some calculations I'm trying to realise.
I'm trying to retrieve information based on three tables and then calculate the difference between 2 dates; and then calculating that by a hire rate.

Select O.OrderNo, VO.Hire_Start_Time, VO.Hire_End_Time, V.Vehicle_No, V.Hire_Rate, VO.Hire_End_Time - VO.Hire_Start_Time * V.Hire_Rate
From Order O, Vehicles_Ordered VO, Vehicles V
Where O.Order_No = VO.Order_No
And V.Vehicle_No = VO.Vehicle_No
And O.Order_No = 3

I can't seem to make it work. Im not sure If it's because I've made an error with my joins, or because I've made a mistake with the final select statement.
Any help would be much appreciated.
Thanks

[Updated on: Mon, 10 December 2007 16:32]

Report message to a moderator

Re: Calculating from multiple tables [message #286991 is a reply to message #286990] Mon, 10 December 2007 16:37 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Exactly what help do you expect? We don't know what you are trying to do, if you supply a test case i.e. create table and insert statements to create a valid test case along with expected results and the logic behind those results we will be able to help.
And please read the sticky at the top of the forum list.
Re: Calculating from multiple tables [message #286992 is a reply to message #286990] Mon, 10 December 2007 16:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
In some/many/most programming languages multiplication take precedence over addition or subtraction.
Try the following instead -
(VO.Hire_End_Time - VO.Hire_Start_Time) * V.Hire_Rate
Re: Calculating from multiple tables [message #286997 is a reply to message #286990] Mon, 10 December 2007 17:24 Go to previous messageGo to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
Thanks for the replies.
I've read the sticky now and I have spent some time searching already.

I want to join all the information from Order No 3 (from the Order table). Select the Time the vehicle has been hired for from vehicle_ordered. And select the Hourly hire rate from the vehicle table.
From my sample data I know that
(Vehicle_Ordered) Hire_Start_Hire = 10.00 06-09-07(24HH)
(Vehicle_Ordered) Hire_End_Hire = 14.00 06-09-07(24HH)
(Vehicle)Hourly_Hire_Rate 175

So the total should be 700.

The relevant Table and Insert data are below.

CREATE TABLE Vehicle
(
Vehicle_No CHAR(10) NOT NULL,

Hourly_Hire_Rate NUMBER(6,2) NULL Constraint Hourly_Hire_Range CHECK (Hourly_Hire_Rate between 10 and 500),
primary key (Vehicle_No)
);

INSERT INTO Vehicle VALUES (8, 175.00)

create table Vehicles_Ordered

(Vehicle_No CHAR(4) NOT NULL,
Staff_No CHAR(4) NOT NULL,
Order_No CHAR(4) NOT NULL,
Hire_Start_Time DATE NOT NULL,
Hire_End_Time DATE NOT NULL,

primary key (Vehicle_No, Staff_No),
foreign key (Vehicle_No) references Vehicle(Vehicle_No),
foreign key (Staff_No) references Staff(Staff_No),
foreign key (Order_No) references Order_Record(Order_No));

INSERT INTO Vehicle_Ordered VALUES
(‘8’ ,'9', '3',

TO_DATE('07-SEP-06:10:00','YY-MON-DD:HH24:MI'),
TO_DATE('07-SEP-06:14:00','YY-MON-DD:HH24:MI'),

CREATE TABLE Order_Record
(Order_No CHAR(4) NOT NULL,
primary key (Order_No),
foreign key (Customer_No) references Customer(Customer_No)
);
INSERT INTO Order_Record VALUES
(3);

I have been worrying at this query for a while now and I'd be happy to keep at it, But it's getting a little time sensitive now and Im starting to worry.

Thanks.

[Updated on: Mon, 10 December 2007 17:32]

Report message to a moderator

Re: Calculating from multiple tables [message #286998 is a reply to message #286997] Mon, 10 December 2007 17:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Thanks for the test case
The calculation would be along the lines of

(your_end_time-your_start_time)*24*your_hourly_rate

other than that, your query looks ok
Re: Calculating from multiple tables [message #286999 is a reply to message #286998] Mon, 10 December 2007 17:48 Go to previous messageGo to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
"*24*your_hourly_rate"

That really didn't occur to me, Thanks.
I'll try it tomorrow when I've got access to Oracle again
Re: Calculating from multiple tables [message #287063 is a reply to message #286997] Tue, 11 December 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I've read the sticky

Not so much: you didn't see how to format your post, you didn't see you have to post your Oracle version (4 decimals).

Regards
Michel
Re: Calculating from multiple tables [message #287221 is a reply to message #286997] Tue, 11 December 2007 08:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
[quote title=berniecliffdon wrote on Mon, 10 December 2007 18:24]

[b]INSERT INTO Vehicle_Ordered[/b] VALUES
        (‘8’ ,'9', '3', 

TO_DATE('07-SEP-06:10:00','YY-MON-DD:HH24:MI'),
TO_DATE('07-SEP-06:14:00','YY-MON-DD:HH24:MI'),


Invalid syntax. You never closed out the command.


[edit] I had one too many lines of invalid syntax.

[Updated on: Tue, 11 December 2007 09:05]

Report message to a moderator

Re: Calculating from multiple tables [message #287234 is a reply to message #286990] Tue, 11 December 2007 08:59 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You may want to read up on the syntax for adding Constraints

Hint: Alter Table, add constraint

[Updated on: Tue, 11 December 2007 09:00]

Report message to a moderator

Re: Calculating from multiple tables [message #287239 is a reply to message #287234] Tue, 11 December 2007 09:32 Go to previous messageGo to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
I think the problem is with my tables and insertions myself. Im having other problems too. I'll work on correcting that before attempting the query again.
Many thanks everyone.

Here's my Oracle version if anybody would like to know.
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
Re: Calculating from multiple tables [message #287577 is a reply to message #286990] Wed, 12 December 2007 14:16 Go to previous messageGo to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
I've spent the last couple of sessions Correcting the faults in my tables and Inserts and they all seem fine now.
I've tested the query changes that have been suggested and I can see that they should work. Im still having a couple of problems though.
The first problem is that I completely failed to appreciate some thing about my orders; namely that some orders have more than one journey to account for (i.e. An Order my Involve 3 different cars and so the price would be End_time - Start_Time on 3 records)
The solution I have attempted is.

Select SUM((Vehicles_Ordered.Hire_End_Time-Vehicles_Ordered.Hire_Start_Time)*24*Vehicle.Hourly_Hire_Rate)    
From Customer, Order_Record, Vehicles_Ordered, Staff, Vehicle
Where Customer.Customer_No = '5'
And Customer.Customer_No = Order_Record.Customer_No
And Order_Record.Order_No = Vehicles_Ordered.Order_No
And Vehicles_ordered.Staff_No = Staff.Staff_No;


Unfortunately this produces a "missing right parenthesis" error message. When I simplify the query to include my own number to multiply by (e.g. 4) outside of the parenthesis and without the sum, then the query works. As below

Select (Vehicles_Ordered.Hire_End_Time-Vehicles_Ordered.Hire_Start_Time)*24*4 
From Customer, Order_Record, Vehicles_Ordered, Staff, Vehicle
Where Customer.Customer_No = '5'
And Customer.Customer_No = Order_Record.Customer_No
And Order_Record.Order_No = Vehicles_Ordered.Order_No
And Vehicles_ordered.Staff_No = Staff.Staff_No;


It works, but It produces 3 records and not 1 total.

In Summary, I can't make the sum work and I seem to have a join problem with my Vehicle.Hourly_Hire_Rate value.

Im sorry about the length of the post and my lack of knowledge about this.
I said two days ago that time was sensitive. Now Time is so sensitive it wears all black and writes bad poetry. So any advice would be fantastic.
Thanks

(Edited format to display code)

[Updated on: Wed, 12 December 2007 15:03]

Report message to a moderator

Re: Calculating from multiple tables [message #287581 is a reply to message #286990] Wed, 12 December 2007 14:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow the posting guidelines as stated in URL below
http://www.orafaq.com/forum/t/88153/0/
Re: Calculating from multiple tables [message #287582 is a reply to message #287581] Wed, 12 December 2007 15:16 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
WHat does the staff table look like ?
Re: Calculating from multiple tables [message #287583 is a reply to message #287582] Wed, 12 December 2007 15:20 Go to previous messageGo to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
CREATE TABLE Staff
(Staff_No 		CHAR(5) 	NOT NULL,
Title			CHAR(4) 	NOT NULL,
First_Name 		VARCHAR2 (15) 	NOT NULL,
Surname 		VARCHAR2 (15) 	NOT NULL,
House_Number 		VARCHAR2 (15) 	NOT NULL,
Street 			VARCHAR2 (30) 	NOT NULL,
Town 			VARCHAR2 (30) 	NOT NULL,
Postcode 		CHAR(8) 	NOT NULL	UNIQUE,
Contact_Number 		CHAR(12) 	NOT NULL,
National_Insurance_No 	CHAR(10) 	NOT NULL 	UNIQUE,
Driving_License_No	CHAR(16)	NOT NULL 	UNIQUE,
Date_Joined_Company 	DATE 		NULL,
Hourly_Rate 		DECIMAL(8,2) 	NOT NULL 
  constraint HourlyRateRange CHECK (Hourly_Rate between 0 and 50),
Position 		VARCHAR2(30) 	NOT NULL,
Primary key (Staff_No));


I can post all my tables If It might be relevant.
The only Reason staff is involved in the join at all is because I was going to attempt a query that calculated the cost, then remove the total wages paid for the order.
I've given up on that idea, so I will remove that join on my next attempt.

[Updated on: Thu, 13 December 2007 00:34] by Moderator

Report message to a moderator

Re: Calculating from multiple tables [message #287585 is a reply to message #286990] Wed, 12 December 2007 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
From http://www.orafaq.com/forum/t/88153/0/
Quote:


Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)

Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.

Provide your expected result set and explain the rules/reasons that lead to it.

If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:


Re: Calculating from multiple tables [message #287588 is a reply to message #286990] Wed, 12 December 2007 17:16 Go to previous messageGo to next message
berniecliffdon
Messages: 7
Registered: December 2007
Junior Member
I've attached all my table creation and insert statements. Im not sure if they're in the correct format for easy entry (I have re-read the FAQ and searched for "DDL format")

The way it reads is.

Create table
etc.
);

Insert
(several inserts)
);

Repeated

if this isn't correct please let me know.
Unfortunately Im away from Oracle at the moment, So I can't use that to create DDL or state the error message's exactly.

The result I expect based on The Ordered_Vehicles for Customer_No 5 is
Vehicle_No 9: Hourly_Hire_Rate 250 * 4 hours = 1000
Vehicle_No 5: Hourly_Hire_Rate 150 * 4 hours = 600
Vehicle_No 2: Hourly_Hire_Rate 145 * 4 hours = 580
The SUM of which should be = 2180

This is the code I used to try to achieve it, Without the Staff join I had previously.

Select SUM((Vehicles_Ordered.Hire_End_Time-Vehicles_Ordered.Hire_Start_Time)*24*Vehicle.Hourly_Hire_Rate)    
From Customer, Order_Record, Vehicles_Ordered, Staff, Vehicle
Where Customer.Customer_No = '5'
And Customer.Customer_No = Order_Record.Customer_No
And Order_Record.Order_No = Vehicles_Ordered.Order_No;


On my next attempt I am going to state that Vehicle_No is linked between Vehicle and Vehicles_Ordered like so. Because I think I should have done that originally.

Select SUM((Vehicles_Ordered.Hire_End_Time-Vehicles_Ordered.Hire_Start_Time)*24*Vehicle.Hourly_Hire_Rate)    
From Customer, Order_Record, Vehicles_Ordered, Staff, Vehicle
Where Customer.Customer_No = '5'
And Customer.Customer_No = Order_Record.Customer_No
And Order_Record.Order_No = Vehicles_Ordered.Order_No
And Vehicles_Ordered.Vehicle_No = Vehicle.Vehicle_No;
Re: Calculating from multiple tables [message #287783 is a reply to message #287588] Thu, 13 December 2007 07:56 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Unless I'm missing something about your requirements,

This seems to work


SQL> select * from order_record;

ORDER_NO   CUSTOMER_N
---------- ----------
3          5

SQL> select * from vehicle;

VEHICLE_NO HOURLY_HIRE_RATE
---------- ----------------
8                       175
3                       400

SQL> select * from vehicles_ordered;

VEHICLE_NO STAF ORDER_NO   HIRE_STAR HIRE_END_
---------- ---- ---------- --------- ---------
8          9    3          06-SEP-07 06-SEP-07
3          1    3          01-DEC-07 01-DEC-07

SQL> select  
sum((Vo.Hire_End_Time-Vo.Hire_Start_Time)*24*v.hourly_hire_Rate) 
as invtotal
  2  from     Customer c,
  3       Order_Record o,
  4       Vehicles_Ordered vo,
  5       vehicle v     
  6  where C.Customer_No = O.Customer_No and 
  7  o.order_no =   vo.order_no and 
  8  v.vehicle_no = vo.vehicle_no;

  INVTOTAL
----------
      1100


Previous Topic: Is DMBS_LOCK.REQUEST First-In First-Out?
Next Topic: is there any better way of doing this
Goto Forum:
  


Current Time: Sun Dec 04 00:33:02 CST 2016

Total time taken to generate the page: 0.12316 seconds