Re: date format and nesting

I realise i have to put another where clause for works hours. I have tried evrything I possibly know, anyway the project was due at on the 15th and i have submitted the the work. Ill know the answer soon enough and of course ill then realise where i made the mistake and kick my own but,

but $50 hey come on.

Louis

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3cbbda6d$0$236$ed9e5944_at_reading.news.pipex.net...
> 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 - 07:32:22 CDT