Re: Help Urgent...Select Statement

I missed that one (from previous answer). Sorry answered to quick.

Hmmm....Nested aggregation. Never tried this. Not sure whether you can do it at this level but made me think anyway.

If not then line 2 has missing bracket which is on line 6. In this case the query won't work because the man time will be multiplied by the number of parts which is not want you want. The problem is you have two aggregation levels which I assume, explains your nested aggregation.

Inelegant solution would be to divide the man time by count(*). The elegant solution is to use a from select e.g. [from (select ...), ] to aggregate at the required levels then join the results. I would code it but I haven't got time, sorry. Anyway it it's good practice to have go and see what happens. I'm sure someone else will explain it in great detail to me and you.

I'll look into nested aggregations. Not much in the manual about this.

Best of luck.

grateful1_at_my-dejanews.com wrote:

> I have an assignment to complete for a beginner course in Oracle (that will
> only be 45 hrs total). I am having a problem with the script I created.
> Could someone please help? This is the question asked: There are 4
> technicians in the company: John, Maria, Nancy and Steve with the following
> hourly rates: John $25/hour, Maria $32/hour, Nancy $22/hour, Steve $30/hour.
> For every service call, create a SELECT statement that will provide the
> total cost(parts + labour).
>
> Here is the script I have up to now with the error it gave:
>
> SQL> SELECT own.name, photoc.Photocopier_Id, serv.Sc_Number,
> 2 SUM(prt.Prize +
> 3 SUM(DECODE
> 4 (det.Technician, 'John', 35, 'Maria', 32,
> 5 'Nancy', 22, 'Steve', 30, 0)
> 6 * det.Hours_Worked)) "Cost"
> 7 FROM Owners own, Photocopiers photoc, Parts prt,
> 8 Sc_Details det, Service_Calls serv
> 9 WHERE serv.Sc_Details = det.Sc_Number
> 10 AND det.Part_Id = prt.Part_Id
> 11 AND serv.Photocopier_Id = photoc.Photocopier_Id
> 12 AND photoc.Owner_Id - own.Owner_Id
> 13 GROUP BY own.Name, photoc.Photocopier_Id, serv.Sc_Number;
> GROUP BY own.Name, photoc.Photocopier_Id, serv.Sc_Number
> *
> ERROR at line 13:
> ORA-00920: invalid relational operator
>
> Can someone please help me with this error, I have tried to correct this
> without success. Thank you for your time. P.S. it is due on Friday.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Mar 04 1999 - 09:22:26 CST