Re: Help Urgent...Select Statement

On Thu, 4 Mar 1999 grateful1_at_my-dejanews.com wrote:

> In article <36DE995F.56099076_at_capgemini.co.uk>,
> Mike Burden <michael.burden_at_capgemini.co.uk> wrote:
> > Are you missing a close bracket on line 2.
> >
> Actually the closing bracket is on line 6.
>
> > Looking at the query I would of thought the hourly rate should be held on
> > Sc_Details table, and so remove the need for a decode.
>
> I am not sure I understand, the Hours are in the SC-Details table but not the
> hourly rate it is the amount of hours worked. Here is a description of the
> tables:
>
> SC_details
> -----------
> Sc_number NOT NULL Number(6)
> Line_ID NOT NULL Number(6)
> Part_ID Number(6)
> Service Varchar2(255)
> Technician Varchar2(30)
> Hours Number(6)
>
> For those of you who caught my '-' mistake, it was corrected with '='. Thank
> you. But it still did not solve the problem. Am I missing an easier way of
> doing 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
> >
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>

In my earlier reply I just concentrated on your ORA-problem and when I now focus on the problem in whole I must confess I don't really get what you want to do with your script.

You say it didn't solve your problem. In what way? If I am on the right track you got a to big sum from your query. Am I???? In that case you don't need both (perhaps none) of your sum-clauses. But I might be out in the blue here, in which case I need more information to be of any help.

tomhag_at_rsv.se Received on Fri Mar 05 1999 - 02:58:41 CST