that should read where clause for hours.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:3cbb35f7$0$230$cc9e4d1f_at_news.dial.pipex.com...
> you don't have a where clause so you won't have any conditions, to_char
> converts other formats (number/date etc) to character in the format given
by
> the format mask. Further homework help is chargeable at 50 dollars per
hour
> of students time.
>
> --
> Niall Litchfield
> "Louis Dumont" <dumontls_at_dumontls.com> wrote in message
> news:4HQt8.2442$o66.5607_at_news-server.bigpond.net.au...
> > I have the following statements and am finding difficulty doing 2 things
> >
> > formatting the date to 22-March-2002 and producing only the highest
hours
> > worked for each day, any ideas here please
> >
> > SELECT TO_CHAR((N.PERS_NAME) || ' a ' || (P.POSN_NAME) || ' worked ' ||
> > A.ACTIV_HOURS || ' hours in ' || (U.UNIT_NAME) || ' on ' ||
> (A.ACTIV_DATE))
> > FROM POSITION P,PERSONNEL N, ACTIVITY A, UNIT U WHERE
P.POSN_ID=N.POSN_ID
> > AND N.PERS_ID=A.PERS_ID
> > AND A.UNIT_ID=U.UNIT_ID
> > ORDER BY A.ACTIV_DATE
> >
> > Which gives me the following result
> >
> >
>
TO_CHAR((N.PERS_NAME)||'A'||(P.POSN_NAME)||'WORKED'||A.ACTIV_HOURS||'HOURSIN
> > '||(
>
> --------------------------------------------------------------------------
> --
> > ----
> > LEAR a LECTUER A worked 5 hours in I.S. 1 on 22-MAR-02
> > EVERETT a TUTOR worked 5 hours in I.S. 1 on 22-MAR-02
> > MENG a LECTUER B worked 4 hours in LAW 1 on 22-MAR-02
> > FOWLER a LECTUER A worked 5 hours in LAW 1 on 22-MAR-02
> > KENNETT a LECTUER A worked 4 hours in LAW 1 on 22-MAR-02 RICHARDSON a
> > LECTUER B worked 4 hours in MANAGEMENT 1 on 22-MAR-02 HEUNG a LECTUER A
> > worked 6 hours in MANAGEMENT 1 on 22-MAR-02 SMITH a LECTUER A worked 3
> hours
> > in ACCOUNTING 1 on 22-MAR-02 DAVILLIA a TUTOR worked 5 hours in
ACCOUNTING
> 1
> > on 22-MAR-02
> >
> > However I need to only output the person who wrks the most hrs for each
> day,
> > and have tried putting Max(a.activ_hours) in the select statement, it
> doesn'
> > t work.
> >
> > Any help here
> >
> > Also for the date I have to output in 22_march 2002 format and tried
using
> > Fm(a.activ_date, 'DD-Month-YYYY') alos tried fmFormat(...) and
> To_Date(...)
> > it comes up with an error.
> >
> > Ideas pls
> >
> >
> >
>
>
Received on Tue Apr 16 2002 - 03:01:48 CDT
