desperate help needed

[evodan1986]

Hello, ive been trying this code for hours and i have sofar managed to enter the code correctly( i think) but every time a message stating"no data has been found" is shown. I have inserted all the data required. Below is the code i created:

 

SELECT Customer_Name, RepairRequest.RequestId, Date_Raised, SUM(Price * Quantity_Used) AS PartCosts

FROM Cust

INNER JOIN RepairRequest ON

CUST.Customer_Id = RepairRequest.Customer_Id

INNER JOIN PartUsed ON

PartUsed.RequestId = RepairRequest.RequestId

INNER JOIN Part ON

Part.PartRef = PartUsed.PartRef

 

GROUP BY Customer_Name, RepairRequest.RequestId, Date_Raised

 

I need to produce a list of customers, the repair requests thay have made and the total value of any parts used for each reapir request. The list should include the customers name, repair request ID, date raised and value of parts used. And finally i need to ensure that list is in reverse value of parts used within customer - HOw complicated is that?!

 

I a really struggling with this question and any help would be very very very appreciative. Below are the tables and insert statements  i made:

 

CREATE TABLE CUST

(CUSTOMER_ID NUMBER( PRIMARY KEY,

CUSTOMER_NAME VARCHAR2(60),

CUSTOMER_ADDRESS VARCHAR2(100),

CUSTOMER_TELEPHONE VARCHAR2(22)

);

 

CREATE TABLE PART

(PARTREF VARCHAR2(7) PRIMARY KEY,

DESCRIPTION VARCHAR2(100),

PRICE NUMBER(7,2),

STOCK_QUANTITY NUMBER(2)

);

 

CREATE TABLE PARTUSED

(PARTREF VARCHAR2(10),

REQUESTID VARCHAR2(10),

QUANTITY_USED NUMBER(10),

PRIMARY KEY(REQUESTID,PARTREF),

FOREIGN KEY (REQUESTID) REFERENCES REPAIRREQUEST(REQUESTID),

FOREIGN KEY (PARTREF) REFERENCES PART(PARTREF)

);

 

 

CREATE TABLE REPAIRREQUEST

(REQUESTID VARCHAR2(10) PRIMARY KEY,

CUSTOMER VARCHAR2(30),

CONTRACT_NO VARCHAR2(10),

SERIAL_NO VARCHAR2(10),

DATE_RAISED DATE,

PLANNED_START VARCHAR2(20),

PLANNED_END VARCHAR2(20),

ENGINEER CHAR(20),

CUSTOMER_ID NUMBER( REFERENCES CUST(CUSTOMER_ID),

STAFFNO NUMBER(3) REFERENCES ENGINEER(STAFFNO)

);

 

CREATE TABLE ENGINEER

(STAFFNO NUMBER(3) PRIMARY KEY,

STAFFNAME VARCHAR2(20),

GRADE CHAR(10)

);

 

 

INSERT INTO CUST(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_TELEPHONE)

VALUES ('3281','MRS J SMITH','28 SCHOOL LANE DONCASTER S85 3UT','01302 136544')

 

INSERT INTO CUST(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_TELEPHONE)

VALUES ('4811','EFFICIENT HEATING','17 HIGH STREET ROTHERHAM S60 4ES','01709 654789')

 

INSERT INTO CUST(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_TELEPHONE)

VALUES ('5977','MR K BROWN','55 THE MOOR SHEFFIELD S1 2SR','01142 625876')

 

 

 

INSERT INTO PART(PARTREF,DESCRIPTION,PRICE,STOCK_QUANTITY)

VALUES ('127-52','VIGLEN V45-A COOLING FAN','15.50','12')

 

INSERT INTO PART(PARTREF,DESCRIPTION,PRICE,STOCK_QUANTITY)

VALUES ('235-005','HP1530 PAPER FEED ROLLER','24.75','9')

 

INSERT INTO PART(PARTREF,DESCRIPTION,PRICE,STOCK_QUANTITY)

VALUES ('369-999','MAINS CABLE 1 METRE','3.50','53')

 

 

INSERT INTO PARTUSED(PARTREF,REQUESTID,QUANTITY_USED)

VALUES ('127-52','06/01256','1')

 

 

INSERT INTO REPAIRREQUEST(REQUESTID,CUSTOMER,CONTRACT_NO,SERIAL_NO,DATE_RAISED,PLANNED_START,PLANNED_END,ENGINEER)

VALUES ('06/01256','MR K BROWN','05-2256/01','JH765H/5','06/FEB/06','17/FEB/06 AM','17/FEB/06 AM','JESS OAKROYD')

 

INSERT INTO REPAIRREQUEST(REQUESTID,CUSTOMER,CONTRACT_NO,SERIAL_NO,DATE_RAISED,PLANNED_START,PLANNED_END,ENGINEER)

VALUES ('06/01258','EFFICIENT HEATING','07-2011/01','1258/K/8','16/FEB/06','17/FEB/06 PM','17/FEB/06 PM','MIKE MENDIT')

 

 

INSERT INTO ENGINEER(STAFFNO,STAFFNAME,GRADE)

VALUES ('179','JESS OAKROYD','ENGINEER')

 

INSERT INTO ENGINEER(STAFFNO,STAFFNAME,GRADE)

VALUES ('180','MIKE MENDIT','ENGINEER')

 

INSERT INTO ENGINEER(STAFFNO,STAFFNAME,GRADE)

VALUES ('167','DAN WEBB','ENGINEER')

 

If anyone can help I will be very thankfull, look forward to reading some helpful replies.

 

Cheers

 

Dan.

[andrewgauger]

You need to define from which tables the data is from:

 

SELECT Cust.Customer_Name, RepairRequest.RequestId, RepairRequest.Date_Raised, SUM(part.Price * partused.Quantity_Used) AS PartCosts

 

 

[evodan1986]

I thought the tables are defines in the select statement: the cust table,repair request id date raised and an alias of parts cost. When i select * from cust and the other tables the data is dispalyed but these code results in no data found?

 

SELECT Cust.Customer_Name, RepairRequest.RequestId, RepairRequest.Date_Raised, SUM(part.Price * partused.Quantity_Used) AS PartCosts

FROM Cust

INNER JOIN RepairRequest ON

CUST.Customer_Id = RepairRequest.Customer_Id

INNER JOIN PartUsed ON

PartUsed.RequestId = RepairRequest.RequestId

INNER JOIN Part ON

Part.PartRef = PartUsed.PartRef

GROUP BY Customer_Name, RepairRequest.RequestId, Date_Raised

ORDER BY PartCosts DESC